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?)
Solved! Go to Solution.
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.
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 !
@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.
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 !