Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

TONUMBER lost information in conversion

JJXVI123
5 - Atom

Hello,

 

I am trying to create a workflow that will separate out totals of three columns represented by the data point share class. I am having a problem converting the string into a number (double) because of the $ in front of the digits. I am using the formula  tonumber(replacechar([$ DIFF],"$,","")) but I am getting a TONUMBER: lost information in conversion error. I suspect this is because the $ DIFF column has spaces. I tried to use the TRIM formula but I cannot get it to work and I suspect I am doing something wrong. How can I add TRIM to the formula I have already created.

 

5 REPLIES 5
fmvizcaino
17 - Castor
17 - Castor

Hi @JJXVI123 ,

 

If you want to trim right after replacing the char, you can use like this:

tonumber(trim(replacechar([$ DIFF],"$,","")))

 

If this function still doesn't work, please share a sample of your dataset for me to look into it.

 

Best,

Fernando V.

JJXVI123
5 - Atom

Thank you very much  but it looks like that was not the problem, I needed to create another workflow line for $ DIFF and that has mostly worked, the problem I am having now is that the $ DIFF column has negative numbers represented by parenthesis EX (1.09). When I run the formula for tonumber(replacechar([$ DIFF],"$,","")) it is removing the $ but it is also turning the negative numbers into 0's and I am not sure why.

PhilipMannering
16 - Nebula
16 - Nebula

Almost there. Try this,

 

 

tonumber(replacechar(replacechar([Field1],"$,) ",""), '(', '-'))

 

fmvizcaino
17 - Castor
17 - Castor

Hi @JJXVI123 ,

 

Alteryx cannot convert automatically numbers with parenthesis into negative numbers. We need to remove it manually as example attached.

 

fmvizcaino_0-1590694082584.png

 

 

Best,

Fernando Vizcaino

jarrod
ACE Emeritus
ACE Emeritus

Hi @JJXVI123 ,

As a leader in the Alteryx Community, I have the ability to identify & mark accepted solutions on behalf of community members - and recently did so on this thread. If you have any questions or concerns with the solution(s) I selected please let me know by replying to this post.

As the original author, you also have the ability to mark replies as solutions! Going forward, I’d encourage you to identify the solution or solutions that helped you solve your problem, as it's a big help to other community members. Learn more about Accepted Solutions here.

Thank you!

Labels