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 ERROR

jboschee
8 - Asteroid

Hi everyone,

 

I'm getting this error when doing a currency conversion.  Below is the formula I'm using for the conversion.

 

'$'+TOSTRING(TONUMBER([GROSS SALES])/TONUMBER([Budget Rate]),2,1,".")

 

In my export, it is returning $1,703.56 when it should be $1,074,245.05

 

 

tonumber error.PNG

 

Any help is greatly appreciated.

 

Thanks!

7 REPLIES 7
CharlieS
17 - Castor
17 - Castor

@jboschee

 

If you have commas in your [GROSS SALES] and [Budget Rate] columns, those will need to be removed for the calculation. Try this:

 

'$'+TOSTRING(TONUMBER(replace([GROSS SALES],",",""))/TONUMBER(replace([Budget Rate],",","")),2,1,".")

jboschee
8 - Asteroid

Unfortunately, it didn't work.  Still getting the error and having the calculation cut off.

CharlieS
17 - Castor
17 - Castor

@jboschee

 

Could you provide an input file with some example values?

jboschee
8 - Asteroid

Hi Charlie,

 

Here's an example of the input and the output I'm receiving.

CharlieS
17 - Castor
17 - Castor

@jboschee

 

Take a look at my attached solution. It runs with no conversion errors for me. Please let me know if this helps.

jboschee
8 - Asteroid

Thanks for all the help Charlie.  I received zero values for all converted to USD values.  tonumber error.PNGI attached a picture of the workflow I'm working with.  #1 - with your formatting and #2 is with your conversion formula 

MarqueeCrew
20 - Arcturus
20 - Arcturus

@jboschee,

 

WebEx is wonderful.

 

  • When you try to store a string value into a number, you generally get 0.  If however, you start the string with numbers, you'll get the numbers until you hit a character not in the set of {0-9 or .}.
  • Keep your incoming data as either fixed decimal or double
  • Convert to string with:
    ToString([My Number], 2, 1, ".")
    Where [My Number] is a number and 2 is the # of desired decimal places and 1 says that you've got thousand "characters" and that your decimal is a dot.
  • Your IN-DB (teradata) is nice, but with ADHD I couldn't take it.  So I added an OUTPUT to a "cache.yxdb" file and moved a UNION tool after your DATA STREAM OUT.  Then I put those tools (not the union) into a container, "LIVE DATA" and disabled it.  Then I added an input tool inside of a "CACHED DATA" container that read the cache.yxdb file.  The output from the INPUT tool was connected to that same UNION.  You can now flip between your containers as needed.

All worked wonderfully.

 

Cheers,
Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels