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

Formatting number with commas and decimals

anwarrington
6 - Meteoroid

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

15 REPLIES 15
danrh
13 - Pulsar

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
6 - Meteoroid

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.

danrh
13 - Pulsar

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 :)

anwarrington
6 - Meteoroid

Oh my goodness! This worked! I had to use a select tool before my regex formula tool to convert the fields to string so the regex would work. I snuck a select tool in front of my select tool preceding the regex formula, and was able to fix the decimals that way. Thank you! Your shot in the dark hit dead on! :)

Niks_diageo
7 - Meteor

Hello Guys, 

 

can you share me the script of screen shot so that i can use it.

anoopdk
7 - Meteor

Hi, here is the approach I tried to get the desired results.

 

Used Multi Field Formula Tool to get the results.Used Multi Field Formula Tool to get the results.

Present_guy
8 - Asteroid

Wow - can this be added as an Alteryx development item? I.e., create a simple parameter to be checked within a tool to add commas to numbers? The idea of needing a multi-field, convert to string and back to number formula is a bit over-the-top for such a simple need.

alexm06
8 - Asteroid

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
7 - Meteor

This is very helpful! Thanks a lot!

Labels