ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

RAG Colors

DigitMac
8 - Asteroid

Hi Team,

 

Please refer to the below sample data

 

FruitColor
BananaBlue
BananaAmber
BananaRed
CherryAmber
CherryRed
MangoRed

 

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 

 

FruitColorFinal Color
BananaBlueGreen
BananaAmberGreen
BananaRedGreen
CherryAmberGreen
CherryRedGreen
MangoRedRed

 

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
13 - Pulsar

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.

 

DigitMac
8 - Asteroid

@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
13 - Pulsar

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
13 - Pulsar

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
17 - Castor
17 - Castor

Hi @DigitMac 

 

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

 

Dan

DigitMac
8 - Asteroid

@danilang @apathetichell 

 

FruitColorFinal Color
BananaBlueGreen
BananaAmberGreen
BananaRedGreen
CherryAmberAmber
CherryRedAmber
MangoRedRed

 

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
13 - Pulsar

 

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
17 - Castor
17 - Castor

Here you go @DigitMac 

 

Like @apathetichell  suggested, summarizing is the way to go

 

danilang_0-1620154991440.png

 

danilang_1-1620155017142.png

 

Dan

DigitMac
8 - Asteroid

@danilang @apathetichell 

 

Thanks for the help so far !

 

FruitColorFinal ColorStatus
BananaBlueGreenNo
BananaAmberGreenYes
BananaRedGreenYes
CherryAmberAmberYes
CherryRedAmberYes
MangoRedRed

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

 

Labels