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

Number Formatting

krobe158
5 - Atom

I have a Text file that I have imported via FLAT ASCII file. 

The text file has invoice prices that are listed in the following format (below) because they are European invoices. 

10000,00

I need the output to form as 

10,000.00 

I have used a formula to replace ',' with '.' however I cannot add in the thousand delimiter. I have tried using ToString([original_value], 2, 1) formula but have had no success.  Any ideas or help would be appreciated. 

9 REPLIES 9
DavidP
17 - Castor
17 - Castor

For tostring to work, the number has to be in a number format first. Try something like this:

 

DavidP_0-1585555663486.png

 

krobe158
5 - Atom

Amazing, thanks. Happy Monday!

JokeFun
8 - Asteroid

hi @DavidP what's the purpose of "Replace([Field1], ',', '.')"

I tried tostring([Field1],2,3) which has the same result. But I believe you have a reason to add the replace function here.

DavidP
17 - Castor
17 - Castor

Hi @JokeFun, you're right, the Replace([Field1], ',', '.') is not needed.

 

I assumed that the ToNumber function would need the decimal separator to be a dot, so I replaced the comma with a dot, but it works without it too.

BenMoss
ACE Emeritus
ACE Emeritus

Hi [at-mention author],

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!

JokeFun
8 - Asteroid

@DavidP Thanks!

Machteld
5 - Atom

Dear Ben,

 

Using Alteryx I have a simular situation.

The programm producing my data has an output in a text file.

The amounts are in European format, and therefore are not recognised as numbers in Alteryx.

As mentioned above I have tried based on the solution mentioned above, but I keep on losing information.

Could you help me with a solution that will keep my data intact?

 

This is an example of what I've tried:

The data going in is a string called salaris and it holds 2876,36 which is 2,876 euro's and 36 cents.

After using tonumber with replace and multiplieing by 1,000 I only have the euro amount and I have lost the cents.

This is the exact formula I used:

tonumber(Replace([salaris],',','.'))*1000

 

Do you know a way to transform the stingdata 2876,36 to 2,876.36 without losing the last two digits?

 

Kind regards,

Machteld

 

 
 

 

 

 

 

 

 

DavidP
17 - Castor
17 - Castor

Hi @Machteld,

 

Number formats in Alteryx don't have a 1000 separator, so if you want your number to be converted to a Double, Float or Fixed Decimal type, it will be displayed as 2876.36

 

You can achieve this with the following formula tonumber(Replace([solaris], ',', '.') and make sure your new field is defined as Double, Float or Fixed Decimal.

 

This is recommended for using the value in calculations or aggregations.

 

If you just want to display the number as 2,876.36 you can use the formula as described earlier in this post, but the result will be a text value. 

 

One more thing to remember: if [solaris] comes in as a String of size 7, using the formula below where you choose to update the value of [solaris] will result in 2,876.3

 

This is because the string is just 7 characters long, so introducing a 1000 separator chops off one of the decimals at the end. That is why I added a Select tool first to increase the size of the string before applying the formula.

 

tostring(tonumber(Replace([Field1], ',', '.')),2,3)

 

Hope this helps.

Machteld
5 - Atom

Hi @David,

 

Thank you for your quick reply. 

If the result would be displayed as 2876.36, that would be perfect.

That's exactly what I need. 

 

The imput in the formula is shown as 2.876,36 and the field length for the imput is set to 254 as is the input to the formula (V_string 254).

 

The message I have making the conversion is salaris: 2.876,36 stopped converting at a comma. It might be invalid.

Because it stops converting at the comma I loose the digits behind the comma and the amount gets devided by 1000.

tonumber(Replace([solaris], ',', '.')  has the result of 2.876. This is the reason I multiplied by 1000.

 

As I am typing this message I realised it might be the 1000 separator causing it to go wrong and it is.

Just wanted to share with you my solution:

 

tonumber(Replace(replace([salaris],'.',''),',','.'))

 

When I take out de 1000 separator first it does work and I get the number 2876.36.

 

Thank you for your time. I really appreciate your quick answer.

 

Labels