Merging 3 queries using 2 merged dimensions

[Former Member]

Dear experts,

I need to create 1 report using 3 queries.

Problem is that Query 1 and  Query 2 do not have common dimension to merge with.

As a solution, I merged it like that using 2 merged dimensions. 

(Query 1 + Query 2) + (Query 2 + Query 3)

I have a problem when I try to sum column in Query 3.

I get #context error.

I am not sure if I should use In ([ID]) or, In (mobile#)

Because 3 of the tables are merged into one...

I would be extremely grateful if you could advise on this method...

[Former Member · Accepted solution]

Hi,

Can you share a piece of your data from the 3 data providers ?

Regards,

Rogerio

[Former Member · Accepted solution]

Hi thank you always for your reply.

I hope you can see this

Merged Dimensions are:

1.  Mobile (Mobile. Query 1 & Mobile. Query 2)

2. ID (ID.Query 2 & ID.Query 3)

I want to do following formula

=1 Where (Isnotnull([Date 4]) In ([Mobile])

But this does not work.

[Former Member · Accepted solution]

Hi,

the relation between the queries are always 1 x 1 ? Besides, can you provide some real data?

Regards,

Rogerio

[Former Member · Accepted solution]

1. I am not sure what you mean by 1 x 1.

Merged Dimensions 1 have 2 dimensions (ID.Query 1 & ID. Query 2)

Merged Dimensions 2 have 2 dimensions (Mobile.Query2 & Mobile.Query3)

Query 1 and Query 3 are not merged directly.

2. I feel uneasy about showing real data online.

Please let me know if the attached table has enough data for you to analyze...

Data Types:

ID = Number

Mobile = Number

Dates = Date

There are basically 4 columns of data from 3 queries

[Former Member · Accepted solution]

By 1 x 1 I mean that there´s only one register for the combination of ID and Mobile ,

for instance for a hypotetic ID A there´s only one Mobile associated .

I was abble to construct a table with 3 dataproviders, with hypotetical data but :

1 - The tables obey the 1 x 1 relantionship;

2 - Only lines common between the 3 dataproviders are shown,(if id A is present in Q1 and Q2, and Mobile(Q2) 99 is associated to an ID and mobile2  (Q3) exists, the line

What I did was :

1 - Merge both ID (Q1 and Q2);

2 - Merge both Mobile (Q2 and Q3);

3 - Create a detail variable , mobile1_det = [Q2].[Mobile] with associated dimension = [MergedcId];

4 - Create another detail variable mobile2_det = [Q3].[Mobile] with associated dimension [Q2].mobile

The other dimensions must be defined as detail variables associated to the correspondent mobile.

then, a dimension dim1 from Q2 should be a detail variable with associated dimension q2.mobile.

Attached is a zip file containing both the wid file and an Excel file with three  tabs, each one of them with one data provider. Rename the .txt  to .zip.Bear in mind that it was done using BO 4.1 SP5

REgards,

Rogerio

3 dataproviders.txt.zip

[Former Member · Accepted solution]

Hi,

Is your Date4 object a detail or directly coming from Query4. If that is the case I would recommend create a Detail object from Date4 associated with Merged Dimension [ID].

Regards

Niraj

[Former Member · Accepted solution]

Thank you...it is clear now to me how to use Detail & associated dimension...