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

Replacing numerical values

bdsibert
6 - Meteoroid

I have two lists that I am joining to form one large dataset. In list one, I would like to scan a column and replace all of the number 1s with the number 5, the 2s with 8, and the 3s with 7. For the second list, I would like to replace all the 1s with 6 and the 2s with 9.  I have tried using the switch and replace functions within the multi-field formula tool with no success. I'm sure this is a simple request for most, but I can't seem to figure it out. Any assistance would be greatly appreciated!

2 REPLIES 2
tom_montpool
12 - Quasar

My first suggestion would be to ensure that the column you are working on is actually storing the number as a numeric data type...this has implications for how you would do the replace. For example, if the column is a string, then you need to replace "1" with "5" whereas if it is integer you would replace 1 with 5.

 

I would probably use an if statement in the Formula tool to do what you want...it would look something like this (assuming your field is a numeric data type)

 

IF [COL]==1 THEN 5

ELSEIF [COL]==2 THEN 8

ELSEIF [COL]==3 THEN 7

ELSE [COL]

ENDIF

 

You could also use the "Find Replace" tool (in the Join toolbox), which works just as the find/replace in Excel.

 

There are other ways, but hopefully one of these does the trick!

 

Have fun!

bdsibert
6 - Meteoroid

Thank you very much, Tom--I greatly appreciate your help.  This is exactly what I was looking for.

 

Thank you again!

Labels