Tableau concatenate fields that match a condition

Question

I need to generate a label field by concatenating 5 fields, but here's the trick: I only want to add strings that match a value, and not add null strings. I have an example below where four fields are named "red, green blue and yellow" The values can be "Y", "N", or "Null". I need to build a label out of all of the "Y" values:

Red, Green, Blue, Yellow

Y, N, Y, Null --> resultant string would be "Red, Blue"

Y, Y, Null, N --> resultant string would be "Red, Green"

N, Null, N, Y --> resultant string would be "Yellow"

What I've currently done is a cascading series of calculated fields where I check two fields, and put that in a temporary field, then I check the temporary field against the next column and make a new temporary field, etc. Like so (abbreviated code):

If Red is Y and Green is Y then Temp1 = "Red, Green"

Elsif Red is Y and Green <> Y  then Temp1 = "Red"

elsif Green is Y and Red <> Y then Temp1 = "Green"

Else Temp1 is 'empty' 

The next calculation compares Temp1 to Blue for each condition and then builds temp2, and so on.

Question: Is this the most efficient way to do it? Or is there some clever code that I am missing? Can I do it all in one calculation without an insane number of if/then combinations? It would be nice to not have so many fields. 

Answer 1

Check with the following formula

//Calculated field you will create
REPLACE(TRIM( STR(IF [Red] = "Y" THEN "RED " ELSE "" END)+
STR(IF [Green] = "Y" THEN "Green " ELSE "" END)+
STR(IF [Blue] = "Y" THEN "Blue " ELSE "" END)+
STR(IF [Yellow] = "Y" THEN "Yellow" ELSE "" END)), " ", ", ")

For more details, you can refer to the Tableau Tutorial or get indepth knowledge about this field through Tableau Certification Course by Intellipaat.

You should also watch this video tutorial on Tableau: