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]

Hi,

There are a good deal of problems in your current logic structure. Let's look at it:

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

ENDIF

Your first statement checks if color is in Amber, Green or Red. That means it can have any of those 3 values - but your next statement looks for Red and Amber. There are no entries which can be True here.  Any entry in Red and Amber was also in Red, Amber and Green. The same is true for your next statement - it will never be True since any True entries would have triggered the first statement. Then finally you need an else statement to clarifies what happens to unmatched colors.

With the amount of entries which stay with their original value I'd probably look for situations where a Color wasn't in my final color set and swap that out - as opposed to changing whatever the current color is. Something like

if [color] !in (Red, Amber, Green) then "Green" else [color] endif

but I'm not sure what colors you don't want appearing.

[JDong]

@apathetichell 

Thanks. Anyway to achieve using this logic for the above use case ? The below does not work as expected !

if [Color] in ('Red', 'Amber','Green') then "Green"
elseif [Color] not in ('Green') and [Color] in ('Red','Amber') THEN 'Amber'
elseif [Color] not in ('Amber', 'Red') and [Color] in ('Red') THEN 'Red'
elseif [Color] not in ('Red', 'Amber', 'Green') then "Blue"
else [Color] endif

Please advise.

[apathetichell]

As noted that cannot work. It is logically impossible.

Here's why:

First If says Is the color Green Red or Amber

second If asks if the color is Red or Amber - that is impossible. If the color was Red or Amber that second IF statement would never have occured. Done. Passed go. Cannot go back. True entities never test the rest of the If statements. They execute and move on to the next part of your workflow.

reiterating:

IF True then ... ELSEIF FALSE AND TRUE then... That second clause is impossible to be TRUE because both parts need to be True and if the second part was True than the previous If clause would have been True.

anything that was TRUE ALREADY EXECUTED WITH YOUR FIRST IF STATEMENT. It is impossible for your following conditions to match. Perhaps you want to combine multiple colors in one line so you want to test if something has multiple colors? As a one record/one record test what you are doing requires something to be both True and False at the same time.

[apathetichell]

Perhaps what you want to do is use a summarize tool to get all the colors in for one entry in one field and then do some kind of matching. If you explain which colors are your dominant colors (ie the ordering) someone can do it.

[danilang]

Hi @JDong 

Can you post an example of what the output should look like, and we can work backwards from there?

Dan

[JDong]

@danilang @apathetichell 

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

Thanks...here is the outcome if you refer to the IF ELSE logic 'Cherry' has to be Amber and not Green as per the previous discussion. The conditions need to work same as in the IF ELSE Statement

[apathetichell]

I can make you a workflow which will generate that result but I have no idea if it's usable for the rest of your dataset nor if it's what you want. There is no way as presently constructed for "red" Bananas to be "Green," "red" Cherries to be "Amber" and "red" Mango to be "red." Id have to test on multiple colors in the same field. I think you want that - but I don't really know.

[danilang · Accepted solution]

Here you go @JDong 

Like @apathetichell  suggested, summarizing is the way to go

Dan

[JDong]

@danilang @apathetichell 

Thanks for the help so far !

Fruit	Color	Final Color	Status
Banana	Blue	Green	No
Banana	Amber	Green	Yes
Banana	Red	Green	Yes
Cherry	Amber	Amber	Yes
Cherry	Red	Amber	Yes
Mango	Red	Red	Yes

If a new Status column is introduced and we say if Blue then final color always blue how will the calculated field change ?

if // [Status] = 'No' THEN "Blue"
//elseif
contains([concat_color],"Blue") then
'Blue'
elseif contains([concat_color],"Amber") then
'Amber'
elseif contains([concat_color],"Red") then
'Red'
elseif contains([concat_color],"Green") then
'Green'
else
""
endif

Please advise