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.

SEAnalyst SEAnalyst · Accepted Answer · 2019-03-06T20:11:03

Here is a solution that concatenates based on the presence of "Y". In cases where the value is "N", Null, or something different, the ELSE will add nothing to the concatenation.

//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)), " ", ", ")

The TRIM() removes any trailing spaces, which could appear if your last word was something other than 'Yellow". The REPLACE() that wraps around the whole statement adds a comma after each word -- replacing the space with a comma and a space.

Hope that helps.

Tableau concatenate fields that match a condition

1 Answers