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
Solved! Go to Solution.
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
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!
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
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 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?
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!