Renaming values within column

[frae]

Hi All, fairly new to alteryx so apologies if this is a slightly rookie question.

To set the scene I have a dataset with ~250k rows. One of the columns called 'description' has around 200 unique values which are repeated many times. I want to individually rename these 200 unique values under 3/4 categories.

For example within the description there are entries such as 'tuna', 'mackerel' and 'mussels' which I would want to simply rename 'seafood'.

What is the best way to do this on Alteryx? If I was on Excel, I would create a table of the unique values and use a vlookup formula.

Any advice would be appreciated.

Thanks

[OllieClarke]

Hi @frae the find and replace tool is very similar to a vLookup in Excel. Plug in your lookup table to the bottom anchor to replace values in the top anchor

Hope that helps,

Ollie

[Ladarthure]

Hi @frae,

to do so, you can also do a table and then do a join with the newly created field, or use a formula like the one below:

if [description] in ('mussels, 'tuna'..) then 'seafood'

elseif [description in (...) then 'b'

else [description] endif

hope it helped!

[frae]

Hi Ollie, thanks for this. Is there a way to create this vlookup table in Alteryx rather than import from excel? Essentially adding a column and manually entering your 'replace' column which you created.

Fearghas

[OllieClarke]

hi @frae you could use a text input to create your lookup table manually (like I did in my attached example). If you want Alteryx to generate your unique list of names to be replaced, then you could use a summarise tool and groupby your column. You can copy the results of a browse tool, and then paste them directly onto the canvas as a text input, which would allow you to quickly add your rename column 

[frae]

Hi Ollie, thanks for this. The text input would work however I need to have the text input associated to each of the 200 unique values. Does this make sense?

Attached the unique values screenshot to give you an idea

[OllieClarke · Accepted solution]

@frae sorry, I can't have been clear enough. If you use a summarise tool to group by your commodity description, then that will give you a unique list of them. you can then use a browse tool to copy these values (and headers) from the results window (here:)

if you then right click the canvas, and click 'paste' Alteryx will create a text input with those 200 commodity descriptions, and you can easily assign them each a replacement value to feed your find and replace tool. Does that make sense?

[Rujin05]

Hi @OllieClarke 

I'm exactly at same step - do you know if anyway Alteryx can combine the two steps of summarize and text input? Right now I have 130+ fields pending such processing and having 260 steps like this is really time-consuming...

Any help appreciated, thanks!