Ignore missing column in formula tool
- 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
Using the formula tool, I want to create a new column that concatenates up to four columns if they exist in the source file.
New Column Name: CAGM_Key
Formula example: [Carrier ID] + "_" + [Account ID] + "_" + [Group ID] + "_" + [Member ID] (If all 4 columns exist)
Alternate formula: [Account ID] + "_" + [Member ID] (If NOT ALL 4 columns exist)
Alternate formula: [Member ID] (if only Member ID is found)
I need to join two data sources together using the CAGM_Key field which will contain only the columns that exist.
The formula tool errors out when a column is not found. How do I determine in advance if a column does not exist?
I have used the Field Info tool to identify what's missing but don't see a way to create then run an expression on the fly.
btw - This could be solved simply by a function within the formula tool like "IfFieldFound"
Suggestions?
Solved! Go to Solution.
- Labels:
- Dynamic Processing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I would suggest a slightly different approach to the problem. I get that you don't know if you can trust that incoming data will contain a REQUIRED element. You want to be flexible. I would use a 'templated' input as a UNION to the incoming data where the template contains all of the REQUIRED elements. After your calculations, you can filter/select remove rows or columns that you don't need.
Now you will always have the 4 columns and your formula will work with minor tweaks
IF IsNull([Account ID]) or IsNull([Group ID]) AND (!IsNull([Account ID]) AND !IsNull([Member ID])) THEN [Account ID] + "_" + [Member ID] ELSEIF ISNull([Carrier ID]) THEN [Member ID] ELSE [Carrier ID]+"_"+[Account ID]+"_"+[Group ID]+"_"+[Member ID] ENDIF
Cheers,
Mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks Mark.
I interpret the solution to be to add a Text Input tool containing the four field named columns, then use a union tool to append the columns to the end of the source file. If the columns do not exist in the source they will be appended. Otherwise they are dropped.
That worked.
Thank you!
