RAG Colors

[JDong]

Hi Team,

Please refer to the below sample data

Fruit	Color
Banana	Blue
Banana	Amber
Banana	Red
Cherry	Amber
Cherry	Red
Mango	Red

I want to create a new column based on the below conditions

If Color in ('Red','Amber','Green') THEN 'Green'
ELSEIF Color in ('Red','Amber') THEN 'Amber'
ELSEIF Color in ('Red') THEN 'Red'

ENDIF

Now the problem is condition fails in my use case since 'Cherry' satisifes 2 conditions 

Fruit	Color	Final Color
Banana	Blue	Green
Banana	Amber	Green
Banana	Red	Green
Cherry	Amber	Green
Cherry	Red	Green
Mango	Red	Red

So if we notice 'Cherry' is colored Green but expected output is Amber.

Any thoughts how to get over this where we consider specific to the values in IF logic (Something like ONLY IF?)

[apathetichell]

the logic there works - assuming:

a) that status is already populated.

b) that you want Blue to trump all other colors

c) an empty field is preferable to some kind of base color.

in a formula you'd have to be clear that you are modifying "final color" and not entering a new field.

[JDong]

@danilang @apathetichell 

Thanks..but in my use case this fails since I have to ensure that 2 combinations of the colors are to be marked

if contains([Concat_Color],"Blue") then
'Green'
elseif contains([Concat_Color],"Amber") then
'Amber'
elseif contains([Concat_Color],"Red") then
'Red'
else
""
endif

Conditions :

BLUE = No Red, No Amber, No Green
RED = Red
AMBER = Red, Amber
GREEN = Red, Amber,Green

Please advise based on the conditions...thanks !

[apathetichell]

@JDongYou should be able to get what you want by a simple edits to @danilang 's workflow.

Open up his formula - and swap out the first color tested from "Blue" to "Green" and then change the final else from "" to "Blue"

if contains([Concat_Color],"Green") then
'Green'
elseif contains([Concat_Color],"Amber") then
'Amber'
elseif contains([Concat_Color],"Red") then
'Red'
else
"Blue"
endif

This is what I was asking about yesterday in terms of color dominance - it sets "Blue" as the absence of other colors and "Green" as the dominant color.

[JDong]

Hi @apathetichell @danilang 

What helped was a combination :

Step 1:

I created a calculation after summerize as indicated with below logic

LEFT([Concat_Color],15)

Step 2 :

if [Concat_Color] ="Red,Amber" then
'Amber'
elseif [Concat_Color] = "Red,Amber,Green" then
'Green'
elseif contains([Concat_Color],"Red") then
'Red'
else
[Concat_Color]
endif

By doing this I could exactly select 15 characters and the combinations work well for my dataset 😀

Thanks for your support and this worked !