Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Renaming values within column

frae
5 - Atom

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

7 REPLIES 7
OllieClarke
15 - Aurora
15 - Aurora

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

OllieClarke_0-1574764922802.png

Hope that helps,

 

Ollie

Ladarthure
14 - Magnetar
14 - Magnetar

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
5 - Atom

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
15 - Aurora
15 - Aurora

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 

OllieClarke_0-1574766703258.png

frae
5 - Atom

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
15 - Aurora
15 - Aurora

@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:)

OllieClarke_1-1574771576497.png

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?

 

OllieClarke_0-1574771371839.png

Rujin05
7 - Meteor

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!

Labels