Formatting number with commas and decimals

Hello,

I am struggling to format numbers with decimals and commas. My source data, which is an excel file, has all numerical values rounded to 2 with a round formula. When I view the data from the input tool I see two decimal points (10.98). However, after being run through the join tool the formatting changes to (10.980000).

I have tried the tostring function. tostring([input_field),2,1) The out field created for this does not add the commas, or fix the trailing zeroes.

I also tried the regex_replace function. regex_replace([field],"\d{1,3}(?=(\d{3})+(?!\d))","$&,") This formatted fields with comma separators, but does not fix the trailing zeroes.

Has anyone run into this? Any advice or solutions are greatly appreciated!

Anna

Help

Accepted answers

danrh

Have you tried reversing the order of the regex and select tools? Do a select to fixed the decimals, than another select to change it to a v_string, then the regex to add commas. Shooting in the dark a little bit

All comments

danrh

I wasn't able to replicate your issue directly, but for the trailing zeros you could try placing a Select tool after the join and changing the data Type of the field(s) to FixedDecimal with a Size ending in ".2" --- this forces it to retain only 2 decimals. If your regex_replace function is working correctly for the commas then place it after the select and you "should" have the right format.

Dan

anwarrington

Thank you for taking time to look into this.

I tried your suggestion, but it did not work. I had to change the data types to vstring to get the regex function to work. When I tried to use a select tool to change back to fixed decimal after the regex it does not output any values. I also tried using a formula tonumber to change it back, but then my millions get truncated.

alexm06

Another solution for this would be to use the reporting tools.

If you use the Table tool you have the ability to select how many decimals you'd like to output in each column. As long as the data input into the Table tool is a number type, Alteryx will automatically output comma separators too.

KEPM23

This is very helpful! Thanks a lot!

KLAMB

AL-t-E-ry-X - I can see with the Browse tool that my table(s) are formatting these correctly, with the 2 decimal places and automatically placing the commas. But, then when I try to use the Summarize tool and output to an Excel file, the number fields again are unformatted when I open the Excel file.

Shagungupta95

Could anyone please help with the comma separator correct formula to add in values, did try lot of trial and error methods but not helpful.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703