Case statement

[Richard Pobi]

I want to write a case statement that will give me the RESULT column on the attach document. I have three colors (Green, Red and Yellow), ID column is duplicates (4 rows per ID). The color conditional formatting is based on the Amount column. From the Color column, if the ID has all the three colors then that ID is Red. If the ID has only Yellow and Green then Yellow should represent that ID. If the ID has only Green color then ID should be Green. Note, Red color override all the colors, next is Yellow before Green.

Case

when ID=101 AND Color=Red AND Color=Yellow AND Color=Green then Red

when ID=102 AND Color=Yellow AND Color=Green then Yellow

Else Green

Can I use the OVER function if yes, how should I write it Thanks is advance for your assistance

[Gaia Paolini]

Hi

 

go to Edit | Column Properties and select Color.

go to the Sort Order tab

select Custom Sort Order

Configure as Red, then Yellow, then Green.

Press OK.

 

This gives you an ordering of the Color column so that Red is the minimum, followed by Yellow then Green.

Then create a calculated column as:

Min() OVER ([iD])

This should give you an equivalent of the Result you wanted.

Gaia

[Richard Pobi]

Gaia, thanks for your response. Actually your logic is correct, but I have taken care of the issue already. I created a new calculated column (col1) where White = 1, Yellow = 2 and Red = 3. After that I created a second calculated column (col2) as Max (col1) OVER (ID). The next thing is uniquecount (ID) and also Limit Data by the numbers. Limit data (col2) =1, did same for 2 and 3.

 

Thanks for your input.