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

Currency converter

Plewis1
8 - Asteroid

Hello all, I have a full column with a currency but the numbers are long. I am trying to shorten the value up to a normal dollar currency and add a dollar sign symbol. I also want to do this to multiple columns at once. I tried using this formula '$'+Tostring([FIELD],2,1) and I keep getting the error "The formula resulted in a string but the field is numeric". It says to use Tonumber(...) but that's giving me an error as well. Any help is appreciated. Thank you

5 REPLIES 5
RolandSchubert
16 - Nebula
16 - Nebula

Hi @Plewis1 ,

 

the formula results in a string, but the field is numeric, so it will not work within a Formula tool.

Try using the Multi-Field Formula tool. You can change the field type to "String" in the tool and do the conversion for all fields
Configuration could be like below:

05-10-_2019_08-43-58.png

Does this help?

 

Best regards

 

Roland

TonyA
Alteryx Alumni (Retired)

Roland answered while I was putting together an example. I agree the Multi-Field Formula tool is the best way to go.

danilang
19 - Altair
19 - Altair

Hi @Plewis1 

 

The underlying cause of all this is that, unlike in Excel, in Alteryx you can't apply formatting information to a column independently of the column type.  In Excel, formatting details are stored apart from the actual data, so you can take a column of numbers and apply the Currency format without changing the numbers themselves.  In Alteryx, any change you try to make the data is applied to the data itself so it has to match the underlying data type.  Adding a string character like "$" to a number gives you a string result since numeric types can't hold string information, resulting in your "The formula resulted in a string but the field is numeric".  When you followed the advice and added ToNumber() you got the second error message, since ToNumber() doesn't recognize the "$"  as a valid part of a number.

 

Like both @TonyA and @RolandSchubert recommended, you need to store the result in a new string column or convert the existing field to a string.  If you convert the existing field to a string, however, you lose the ability to apply any numeric operations on it.  Multiplying a "$50.00" by 2 will throw an error.  The kind of conversions that you're looking to make are normally applied at the end of a workflow before the output step.  If you want to perform further mathematical operations in Alteryx, leave the numbers as is.

 

Dan 

TonyA
Alteryx Alumni (Retired)

That's a very important point, @danilang. Thanks for catching it.

Plewis1
8 - Asteroid

Thank you all for your help. That worked. 

Labels