Skip to Content
Author's profile photo Former Member

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

/wp-content/uploads/2014/12/tables_603811.png

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

Union.png

[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.

final table.png

Cheers,

Rogerio

Thanks to Mak 1 from BOBJForum for his help

Assigned Tags

      18 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      HI Michael,

      I never tried but I believe that yes, you can do it.

      CHeers,

      ROgerio

      Author's profile photo Former Member
      Former Member

      Hi Michael,

      This works perfect for BW queries, as a workaround for unions and intersections (within BICS connections). Great input Rogelio.

      Regards,

      Rick.

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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 🙂

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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! 🙂

      Author's profile photo Raja M
      Raja M

      Hi, Is this work for reports based on BEX queries, no universe?

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo bansi bobj
      bansi bobj

      Hi Rogerio Plank,

      Thanks for your super explanation with all necessary screenshots by taking simple student example.

      Regards,

      Bansi.

      Author's profile photo Patricia Ciavarelli
      Patricia Ciavarelli

      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!

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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

      Author's profile photo Raja M
      Raja M

      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

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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 )))

       

      Author's profile photo Jay Robertson
      Jay Robertson

      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