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