How to Find Matches e No Matches in Dimensions from 2 Queries (Union, Intersection and Difference)
Hi,
this blog post is about creating Set Operations , at Report side,
There are a few restrictions on using Combined Queries to get Union , Intersection or Minus queries. Some of them are
1 – One cannot perform Combined Queries over different DP;
2 – The objects used on the Result Set couldn´t appear on the query panel;
3 – The objects for both queries must be in the same order, be of the same type and be on the same number (if you retrieve 3 objects from the first query, you must retrieve the same numbger on the other(s) query(ies).
The approach suggested allow users to combine different DPs, with different number of objects and with different types.
As I cannot demonstrate properly how it works using e-fashion, I will use two excel sheets . The Excel sheets represents some People registered for 2 different course, let´s say Course 1 and Course 2.
Course 1 contains the following information :
Student Code, Student Name , Student Addres, specialization and measure From DP1
Here´s the data for Course 1
| student code | student name | student address | specialization |
measure From DP1 |
| 1 | Roger | St 1 13 | Cooking | 1 |
| 2 | Maria | St 2 14 | Engineering | 2 |
| 3 | Nadia | St 3 15 | Writing | 3 |
| 4 | Robert | St 3 16 | Cooking | 4 |
| 5 | Marcus | St 3 17 | Engineering | 5 |
| 6 | Ana | St 4 18 | Writing | 6 |
| 7 | Emerson | St 4 19 | Cooking | 7 |
| 8 | Humberto | St 5 20 | Engineering | 8 |
| 9 | Claudia | St a 21 | Writing | 9 |
| 10 | Roberta | St b 22 | Cooking | 10 |
Course 2 is composed by
Student Code, Student Name , Student Addres, Interest and measure from DP2
and here´s the data for Course 2
| student code | student name | student address | Interest |
measure from DP2 |
| 1 | Roger | St 1 13 | Repolrting | 1 |
| 4 | Robert | St 3 16 | Big Data | 4 |
| 5 | Marcus | St 3 17 | Visualization | 5 |
| 6 | Ana | St 4 18 | Repolrting | 6 |
| 7 | Emerson | St 4 19 | Big Data | 7 |
| 11 | Paulo | St 4 20 | Visualization | 11 |
| 12 | Sergio | St 4 21 | Repolrting | 12 |
| 13 | Margarida | St 4 22 | Big Data | 13 |
| 14 | Nair | St 4 23 | Visualization |
14 |
All objects except [measure from DP1] and [measure from DP2] are dimension, both are measures.
The challenge here is to determine, at report side :
The Union of both queries and display it on a table .([student sode] coming from either queries
The same for Intersection ([Student Code] that belongs to Query 1 and Query 2 simultanesously) , Course 1 – Course 2 (]Student Code] that is in Query 1 but not in Query 2) and Course 2 – Course 1 ([Student Code] in Query 2 but not in Query 1) . Based on student code.
Firstly I will import both Excel sheetsa into WEBI Rich Client which gives the following tables
Then I will merge both DP in [student code], [student name] and [student address] , and Create detail variables for the other dimensions ([specialization det] and [interest det]) based on the merged [student code] dimension
Create two measures variables [Test 1] = If( Not ( IsNull([val1]));1;0) and [Test 2] = If(Not(IsNull([val 2]));1;0)
The Union is given , by default droppoing the three merged dimensions and the detail variables on a table
[Intersection] = If ([Test 1] = 1 AND [Test 2] = 1;1;0)
[Course 1 – Course 2] = If( [Test 1] = 1 AND [Test 2] = 0;1;0)
[Course 2 – Course 1] = If([Test 1] = 0 AND [Test 2] = 1)
Filter by the “1” on the desired Operation ( Intersection , Course 1 – Course 2, or Course 2 – Course 1 ).
Heres the table with all the test.
Cheers,
Rogerio
Thanks to Mak 1 from BOBJForum for his help
Hi Rogerio, can you tell me if this works for BICS connections (SAP BW connection)? I could not get unions and intersections to work on BICS connections.
HI Michael,
I never tried but I believe that yes, you can do it.
CHeers,
ROgerio
Hi Michael,
This works perfect for BW queries, as a workaround for unions and intersections (within BICS connections). Great input Rogelio.
Regards,
Rick.
This is not working for me. When I merge two dimension and then try the IsNull for each of them, it returns always 0 for both.
Hi George. Probably this is because you are using the dimensions itself. You need to compare (Isnull) the metrics, in order to make it work.
Regards, Rick
Hi Ricardo. Thank you very much for your response. Yes actually thats what I am doing. I would like to determine which elements from the dimensions are common in both/exist in one or the other. Do you have any clue how to do that ?
Thank you in advance 🙂
Hi Kan, As you are merging dimensions you can't do it by dimension (as you have one common dimension in both sides), you should use whichever metric instead. Hopefully you make it work.Regards, Rick.
Hi Kan,
Ricardo is right, following this procedure, what you need is , as he said, two metrics, one from each query, then use the IsNull operator in this metrics.
Suppose you got, from Query1 two objects, [dim1] and [measure1], and from Query2 [dim1] and [measure2], and you merge the two queries on [dim1].
When [measure1] IsNull for one value of [dim1], this means that this value did not come from Query1 , equally, if [measure2] IsNull for a value of [dim1] this means that this value did not come from Query2.
Conversely, if the values of [measure1] Is Not Null, the value of [dim1] belongs to Query1, and the same is valid for [measure2] in Query2
Hope this has clarified my explanation,
Any further doubts feel free to contact me.
Cheers,
Rogerio
Guys thank you very much that really helped! I just created a metric for each of the two dimensions and then used this one to do the comparison, as rogerio suggested! Really thank you! 🙂
Hi, Is this work for reports based on BEX queries, no universe?
Hello
thanks for this example and clear explanation.
I've redone it.
then I try to modify the DP by inserting in the first spreadsheet a second specialization "Engiennering" for Roger. Problem : I get a multivalue for the [specialization det].
Is There a workaround ?
thanks
Martin
Hi Rogerio Plank,
Thanks for your super explanation with all necessary screenshots by taking simple student example.
Regards,
Bansi.
Thank you so much for this post! Someone from the BoB forums put me on to this as I was looking to make merged dimensions from custom variables.
This was so useful and easy to get the hang of once I figured it out.
THANK YOU!
Thanks for your comment Patricia,
I take a look at BOB forum and see that Mak suggested you the post.
Glad you like it !!!
Regards,
Rogerio
Hi Rogerio,
Does this work for reports based on BEX queries no universe ? I have 100 customersIDs in query1 and 20 customerIDs in query2. If 18 of 20 query2 customersIDs exist in 100 customerIds of query1 I want to display all 100 customersIds and flag18 as exists in both.
Thanks,
Raja
Hi Raja,
I´m not sure , I hava never worked with Bex , but I think that if Bex queries supports merged dimensions, it should work.
Regards,
Rogerio
Hi Rogerio,
I have a similar requirement
I have 2 data providers Q1 contain Material and Q2 contains ISBN. I am merging both at report level.
(Material and ISBN is list of numbers and both are string)
eg
Material ISBN Formual Needed
111 111 A
121 122 B
123 124 C
Now I want to write a formula based on that saying that
if (Material =ISBN Then “A” (Eg:111)) ElseIf (if the number only in Material then “B”(eg. 121,123) Else (IF the number only in ISBN then “C”(eg.122,124 )))
Thanks Rogerio
This is a truly useful technique, especially when clients consistently want to mix spreadsheet data with tables from a universe.
I used to wonder what in the world the “Detail” qualification was for, but have since found it very useful, in particular for spreadsheet data.
Cheers, Jay