RAG Colors

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?)

Datasets

Developer

Developer Tools

Accepted answers

danilang

Here you go @JDong

Like @apathetichell suggested, summarizing is the way to go

Dan

RAGColors.yxmd

All comments

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.

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703