In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Alteryx-Data Cleanup-Mixed Column throwing Error

keerthy9209
5 - Atom

I have a column TotalPay(datatype V_String) that has values with decimals (23455.88) as well as words(Aggregate). I need to replace these word values with 0 and convert it to datatype double so that i can perform calculations on it.  Tried using Regex_match-> all getting converted to 0, tried if and is_number() , same issue. Please Help. New to Alteryx

4 REPLIES 4
DavidSkaife
14 - Magnetar

Hi @keerthy9209 

 

It's likely due to the fact the column will still be a V_String datatype. As with many things in Alteryx there are multiple ways of doing it, one way is to use a Formula tool to create a new column, and use the below to populate it:

 

IF [OriginalColumn] = "Aggregate"
THEN 0
ELSE TONUMBER([OriginalColumn])
ENDIF

 

binuacs
21 - Polaris

@keerthy9209 you can use the multi-field tool to do the clean-up because you need to make the changes in more than one columnimage.png

 

keerthy9209
5 - Atom

There are more string values in the column. Not just "Aggregate".What to do in this case?

binuacs
21 - Polaris

@keerthy9209 Try Regex_Replace() function and replace the non-numeric to null and apply the ToNumber() function

 

image.png

Labels
Top Solution Authors