Compare the expression values of a single column after pivoting

[#1]

Hi All,

Here is my sample data

Col1	Col2	Col3	Col4
A	Q1	A1	AP1
A	Q1	A2	AP2
A	Q1	B1	BP1
A	Q1	B2	BP2
A	Q1	C1	CP1
B	Q1	A2	AP2
B	Q1	B1	BP1
B	Q1	D2	CP1

And , This is the output that i am looking for

Note:

1. Col1 is pivoted.

2. Expression used = Maxstring(Col4)

Any help is appreciated.

Thanks In Advance.

Message was edited by: Ravi Kumar

Message was edited by: Ravi Kumar

[veidlburkhard]

Hi Ravi,

you have to look for the duplicate values in Col3 and Col4. This is done with the expression 'Sum(Aggr(If(Count(Col4) > 1, 1, 0), Col3))'.

I implemented it in a calculated dimension, though. Which I named 'Result'. See the picture below:

Hope this helps

Burkhard

[Clever_Anjos]

Please find attached

[#4]

Hi Burkhard,

Yes, You are right. We can get the match and no match values based on aggregation count.

The challenge here is , I could not able to apply the appropriate colors as shown in my post.

For Match -- Green

No Match -- Red

Color background expression is not working?

Do you have any thoughts on this?

Thanks for the response.

[Clever_Anjos]

Maybe this

[#6]

Hi Anjos,

No this is not what i am expecting, all the Match values should be in one color and No Match values should be in other color.

Please look at the image i attached in my post for your reference.

Thanks for your response.

[veidlburkhard]

Hi Ravi,

if it is this what you want:

Then please use this formula for the dimension's background color:

=If(Aggr(If(Count(Col4) > 1, 1, 0), Col3) = 1, LightGreen(), LightRed())

and for the expression:

=If(Aggr(NODISTINCT If(Count({$<Col1 =>}Col4) > 1, 1, 0), Col3) = 1, LightGreen(), LightRed())

Hope this helps

Burkhard

[#8]

Hi Burkhard,

Its not working for me either in dimension or expression at least. Below is the screenshot for your reference. Please attach your file if it is working for you.

Thanks for the response..

[veidlburkhard]

Hi Ravi,

see below the details for dimension and expression:

Hope this helps

Burkhard

[#10]

Hi Burkhard,

Its working on the sample data which i have posted above, But the same I applied in my data which is more accurate and it is not working.

Could you please check the attachment once and let me know what i am missing here.

Thanks for the help.