Conditional Formula Look Up
- 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
Hi
I am a real newcomer so excuse the dumb question - I searched on the community but couldn't find an answer ...
I have a column of data some of which is a mix of customer names / 3 letter acronyms ... I would like to write a conditional formula based on the length of cells in that column that says "if the cell contains 3 letters then look up the 3 letter acronym in a lookup type table and replace it with the full name" - the end result being no acronyms are left in the original data ... I guess I need several formulas to achieve that ?
Thanks for the help !
Chris
Solved! Go to Solution.
- Labels:
- Datasets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @ChrisMcN I think a good approach would be to:
Pull on a filter tool and type "Length([Field])=3"
This will separate your column into length=3 and does not....
Now pull a Find Replace tool onto canvas and attach the "T" output of filter to the "F" anchor of Find Replace and attach the lookup table to the "R" anchor.
Find within Field will be the 3 letter acronym to be replaced by the value found in the lookup table...
then you can use a union tool to bring the "F" anchor of the Filter tool back with the Replaced data in the find/replace tool.
*attached a sample workflow
Let me know if any questions!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
hi @ChrisMcN, you could use a join tool to do so, you join on the acronyms, then you will have 3 outputs, one with the records which did not match with your table (L or R depending on your workflow), and on the J you will have the one renamed. Then you can use an union to put the Data together.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @ChrisMcN,
Here is one way of doing this:
- filter for records with length as 3 and join with the lookup (only retain column with the lookup full name)
- join with records that failed the initial filtering (contained full names)
Thanks,
Rafal
#Excuse me, do you speak Alteryx?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @ChrisMcN
Yet another way
Since you have the lookup table, you don't actually need a check for the length of the input. Just join all of them and the use a union to put all the records back together.
Input Becomes
Output
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks so much to you (and everyone on the board !) - that worked perfectly
Chris
