Multiple IFF Conditions
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I would change the ELSE to this ...
ELSE "NOT FOUND" + [Location]
then you can at least see why it's NA
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.