Alteryx-Data Cleanup-Mixed Column throwing Error

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

Table_Snapshot.png

Transformation

Bug

Accepted answers

binu_acs

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

Multi-Field_22042025.yxmd

All comments

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

binu_acs

@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

Multi-Field_22042025.yxmd

keerthy9209

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

binu_acs

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

Multi-Field_22042025.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703