This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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'
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.
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
As noted that cannot work. It is logically impossible.
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.
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.
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.
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.
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