Converting String to Number Results in Negative Numbers becoming zero
- 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
Hi I'm brand new to Alteryx and am having issues converting a string column to values. Snip below of my columns and the resulting output.
- Transactions Amount column is the original string value
- Removed $ column is result of: ReplaceChar([Transaction Amount],"$",""), but still makes this a string data
- Converted Amount column is result of a Tonumber function I found: tonumber(replacechar(trim(regex_replace([Transaction Amount],"^-0{1,}",'-'),"0"),"$,",""),1)
- Converted Removed $ column is me applying the Tonumber function to the Removed $ column thinking the $ character is what was messing it up.
Additionally when I use the select tool and change from tring to double, the negative amounts go to null as well
Solved! Go to Solution.
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @jaypee1217,
Try this formula:
IF StartsWith([Removed $],'(')
THEN ToNumber('-' + REGEX_Replace([Removed $],'\((\d+.*\d*)\)','$1'))
ELSE ToNumber([Removed $])
ENDIF
You could also nest the logic to remove the '$' sign into this formula if you wanted to keep it all in one.
Hope this helps!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
thank you! your simplified ToNumb function worked! I just need to try and next this together now.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks Dan! gautiergodard's function worked when i loaded it. going to try and nest it together now.
