How to Find Matches e No Matches in Dimensions from 2 Queries (Union, Intersection and Difference)

[Former Member]

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