Conditional Formula Look Up

[ChrisMcN]

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

[PeterA1 · Accepted solution]

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! 

[Ladarthure]

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.

[rafalolbert]

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?

[danilang · Accepted solution]

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 

[ChrisMcN · Accepted solution]

Thanks so much to you (and everyone on the board !) - that worked perfectly 

Chris