Alteryx-Data Cleanup-Mixed Column throwing Error
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Bug
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
There are more string values in the column. Not just "Aggregate".What to do in this case?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@keerthy9209 Try Regex_Replace() function and replace the non-numeric to null and apply the ToNumber() function
