Data Retrieval from Multiple Universe Queries

[Former Member]

Hi Everyone....I am relatively new to this programming environment and am still trying to understand how things work so any help would be greatly appreciated!!

I have 3 queries from two universes that I need to find which records in the first two queries are not in the last query.

Query 1               Query 2               Query 3

Universe 1          Universe 1            Universe 2

Num                    Num                    Num

Order                  Order                   Order

Change                                           Change

I know I can merge the queries on the first two elements but I need to display the records in query 1 and 2 that do not have matching elements in query 3.  Any ideas on how to accomplish this?

Thanks!!!

[Former Member]

Hi,

try this :

1 - In each of your queries, bring any measures (let´s say measure1, measure2 and measure 3 for queries 1,2 and 3);

2 - For matches in Q1 and Q2 and not in Q3, use below formula :

3 - merge the three queries on [num order]

[results 1-2 not 3] = If ((not(IsNull([measure1])) OR not(IsNull([measure2]))) AND IsNull([measure 3]);"Desired num order";"Record is also on Query3");

Regards,

Rogerio

[Former Member]

Hi Rogerio!

Thanks for the information...just a few questions for you.

1.  what does [num order] refer to?  Is it a variable?

2.  I am assuming by measures you are referring to any of the available data elements in the universe.

Thanks for all the help!!

[Former Member]

Hi,

I´m assuming the object wchich you´re merging on is [num order].

Yes is any measure object in your universe.

Regards,

Rogerio

[Former Member]

I guess I am too new to webi/business objects.....I am unfamilar with the [num order] object.  I don't see it available in the universes I am using.  Is it an object available to any universe that is created and if so, how do I access it? 

This would be of great help if I can get this to work for several other reports that are waiting to be created.

Thanks!!

Wendy

[Former Member]

Hi Rogerio,

I finally see what you are referring to when you say [num order].  It is the elements I had listed above....sorry it took me so long to make the connection.

I'll see if I can get it to work with what you have given me.

Thanks!!!

[Former Member]

Okay, if you have further questions, feel free to post.

Cheers,

Rogerio

[Former Member]

Hi Rogerio!

I have added a variable to determine whether or not the value was in either of the first two queries and not the third.  It is:

[WhereExistsVariable]=((Not(IsNull([Measure1])) OR Not(IsNull([Measure2]))) And IsNull([Measure3])) Then "Not in Third" Else "In Both"

However, I am getting #MULTIVALUE for some of the entries.  I am displaying the merged values along with each one of the measure items in the report followed by the variable to determine where it exists like:

MergedValue(Num)

MergedValue(Order)

Change

Measure1    

Measure2    

Measure3    

WhereExistsVariable

Any ideas exactly why this is happening?  I do notice that if values of the two merged items appear in the first query but with different change factors it is pulling the measure value from the third query twice even though only one exists in the third query.  Does that make sense?

Query 1

Num Order Change1 Measure1

Num Order Change2 Measure1

Query 2

Record Does not exist

Query 3

Num Order Change1 Measure3

So in this case I shoud recieve "in Both" and I am receiving the #MULTIVALUE error.

Thanks for all your assistance.  It is greatly appreciated!!

Wendy

[Former Member]

Just to be clear:

I should show "In Both" for the first Num Order Change1 combination and "Not in Third" for the second Num Order Change2 combination.

But for both combinations, I am receiving the #MULTIVALUE error with the Measure3 value being repeated for the Num Order Change2 combination that does not exist in the third query.

Hopefully this is clearer.

[Former Member]

Hi Wandy, I´m afraid you don´t have a 1:1 relantionship between your queries, if so, you can try taking a look at . But , in that case  I used just two universes..

Perhaps you could post some sample of your data so its easier to understand exactly the issue.

Regards,

Rogerio

[Former Member]

Hi Wendy,

You can try using Combined Query option in Query Panel.

Below logic could help in your case:

Query 3

MINUS

(QUERY 1 UNION QUERY 2)

This will give you data which is only in Query 3 and not in both Query 1 and Query 2.

Regards,

Yuvraj

[Former Member]

Do I add the combined query on Query 3?

Also, I am not sure how I create the combined query for Query 1 and Query 2 in that tab since they are from a different universe than Query 3.

Thanks for all your help!!!

[Former Member]

Hi Wendy,

Below link might help you to some extent:

https://mars.rdc.noaa.gov/docs/bo_training/KA/AdvRprtDes/course/flash/content/CreatComQuery/wi/xhtml...

The rule for combining query is there should same number of columns in all queries and their data type should be same.

In case the combined query option is not user-friendly for you, you may try to write a custom SQL.

By clicking on SQL button, you can click on Custom SQL and write your own query.

Regards,

Yuvraj

[Former Member]

Thanks Yuvraj!  I took a look at the link and it is great information for combining queries that are using the same universe.  I will bookmark this link for future reference!!

However, what I am trying to do is combine queries that are done on different universes.  I have one universe that I have a query that pulls all num and orders  and another query that uses the same universe but pull the num, orders and changes.  Then a second universe in which I pull the num, orders and changes from it.  These universes deal with different finanical areas which is the reason they are not combined.  Now I need to find which records in the first two queries and not in the third query to see which records have not been accounded for.

How do I go about this?  I have tried to write an independent sql statement that does this but cannot create that query because it goes against more than one universe.

Any ideas would be great!!!

Thanks,

Wendy

[Former Member]

Hi Wendy,

I am glad to know that the link was helpful for you.

I have never come across such a scenario; however, for your case, you may try linking the universes and check.

Through this, you might be able to use objects from different universe in single query and combine them.

Regards,

Yuvraj