Highlight duplicate value in tow different blocks

[former_member198519]

capture.png

Highlight duplicate value in tow different blocks. Please refer to the image attached. Just wanted to know if this is possible.

[kohesco · Accepted solution]

Yes it is, but you will have to merge your common dimension and create a variable or 2 and add conditional formatting

[former_member198519 · Accepted solution]

Thanks Koen. I am using the merged dimension.

Can you please elaborate the solution in little detail?

[kohesco · Accepted solution]

Hi,

I resolved it by doing this:

1. Create merge on SKU number...
2. if you setup the tables as MErged Dimension & Query 2.SKUNumber .. you will see in second column SKU number of query 2 that is contains n° 120.114, but actually it does not, it is because of the merge, so that cannot be used for comparison
3. I had to call two extra variables to do so. I used a dimension that always has an entry for the SKU number and used them to compare. (ie. Lines)

I created 2 detail variables associated to the merged dimension and I created a third one to compare the 2 detail variables:

=If  IsNull([check Query 1])=0 And IsNull([check Query 2])=0 Then "EQ" Else "NE"

If you set your tables like the first screenshot, you 'll see the data per Query and you will be able to highlight the values common in both tables

conditional Format set on the SKU-Number columns - you can hide the not wanted columns as well

[former_member198519 · Accepted solution]

Thanks Koen. Works like a charm, but unfortunately when I apply a Ranking on both the table this logic doesnt work.

Just to let you know, my first table is ranked by Qty(Top 50) and second by Value(Top 50). Is it possible to do with Ranking?

[kohesco · Accepted solution]

Hi,

in the example I play with, the ranking goes fine

I added the price and ranked by TOP3 and you see:

make sure to put the measure of Query 2 in BLock 2 (data query2) and measure of Query 1 in Block 1 (data query 1)

[Former Member · Accepted solution]

Hi,

Attached the screen shot. I have query from 2 DP. one from table(Material)and another from Excel(ITEM)

I am doing an extended merge, so i can get matching as well as unmatching record

The formula i am trying =If IsNull([LWVAL])<>0 Then "A" ElseIf IsNull([V_LastWeek_Data]<>0) Then "B" ElseIf IsNull([LWVAL])<>0 And IsNull([V_LastWeek_Data]<>0) Then "C" Else "NotDefined"

1) 111 contains in both table (Material& Excel)

2)121 only contains in Excel

3)123 only contains in Material

capture1.jpg