Multiple IFF Conditions

[patrickcogley]

I have a data source that I am merge with a tableau extract. Several of my locations fit into three different divisions, Steel, Cylinders, or WAVE. When I joined the datasources I had multiple "NULL" values and I need to move those facilities into their respective divisions. So, I added a formula with multiple IF THEN statements. However, out my 76k records, I am still getting ~22k "na".  What am I doing wrong?

IF [Location] = 'ABERDEEN' THEN "WAVE"
ELSEIF [Location] = 'CHILTON' THEN "Cylinders"
ELSEIF [Location] = 'CLEVELAND' THEN "Steel"
eLSEIF [Location] = 'COLUMBUS (CYLINDERS)' THEN "Cylinders"
ELSEIF [Location] = 'COLUMBUS (STEEL)' THEN "Steel"
ELSEIF [Location] = 'DECATUR' THEN "Steel"
ELSEIF [Location] = 'JEFFERSON' THEN "Cylinders"
ELSEIF [Location] = 'DELTA' THEN "Steel"
ELSEIF [Location] = 'MAIZE' THEN "Cylinders"
ELSEIF [Location] = 'PADUCAH' THEN "Cylinders"
ELSEIF [Location] = 'PORTER' THEN "Steel"
ELSEIF [Location] = 'ROME' THEN "Steel"
ELSEIF [Location] = 'SPARTAN STEEL COATING' THEN "Steel"
ELSEIF [Location] = 'TWB MONROE' THEN "Steel"
ELSEIF [Location] = 'WARWICK' THEN "Cylinders"
ELSEIF [Location] = 'WOOSTER' THEN "Cylinders"
ELSEIF [Location] = 'WSP TAYLOR' THEN "Steel"
ELSE "na"
ENDIF

[Luke_C · Accepted solution]

Hi @patrickcogley 

I would probably handle this by creating a separate mapping table in a text input tool with two columns (City & Division), then join that up to your data using the Join tool or Find and Replace tool. The n/a values indicate that there's something that's not matching your data. Are the cities with NAs covered in your formula? Maybe there's some special characters or leading/trailing spaces?

If you give some sample data we can mock something up.

[cmcclellan]

I would change the ELSE to this ... 

ELSE "NOT FOUND" + [Location]

then you can at least see why it's NA

[Qiu · Accepted solution]

@patrickcogley 

I  agree with @Luke_C .

For this cases, Multiple IIF conditions would be very difficult to debug and mainten.

Better to do with a reference table and Find and Replace function will be helpful.