Skip to Content
0

Web Intelligence - compare two tables from different datasources

Dec 18, 2016 at 05:03 PM

205

avatar image

Hi all,

i need to compare two tables from two different datasources (universes) in web intelligence. Both contains the same fields: user name, logical system, assigned role.

For example table one has three values:

  1. USER1 - R3 - ROLE
  2. USER1 - R3 - ROLE2
  3. USER1 - R3 - ROLE3

The second table:

  1. USER1 - R3 - ROLE
  2. USER1 - R3 - ROLE2

As you can see the last record from the first table is missing in the second table. I need to create a table with same fields user name, logical system, assigned role and an identifier that tells me if the record from first table is present in second. So the result will look like this

  1. USER1 - R3 - ROLE - OK
  2. USER1 - R3 - ROLE2 - OK
  3. USER1 - R3 - ROLE3 - Not OK

Tried merging dimension but its not working. Or maybe i am doing it wrong.

Thanks for any help.

Best regards,

Martin Zluky

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Kuldeep Ghosh Dec 19, 2016 at 11:29 AM
0

Try merging the Username and Logical system. Create a detail and for the Table2 assigned role and compare them. I tried it and worked for the sample data.

Look at the screenshot


sample.jpg (90.3 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

One think I forgot to mention was, in the last table I did checked the option for "Duplicate row aggregation".

0
Martin Zluky Dec 19, 2016 at 03:49 PM
0

Hi Kuldeep,

thanks for your thoughts. It definitely helped me to get on the right way and i am closer and closer to figure it out. But basicly it works like you said. Few changes i made:

  1. There is no need to merge dimensions
  2. I made a dimension variable, not a detail variable for the assigned role from table 2
  3. In query properties i unchecked "Retrieve duplicate rows"

It works for my case, can be different in other cases.

Best regards,

Martin Zluky

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Glad it worked :)

0