Alteryx-Data Cleanup-Mixed Column throwing Error

[keerthy9209]

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

[DavidSkaife]

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]

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

[keerthy9209]

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

[binuacs · Accepted solution]

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