How to Find Matches e No Matches in Dimensions from 2 Queries (Union, Intersection and Difference)
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||
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.
Thanks to Mak 1 from BOBJForum for his help