community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Formatting number with commas and decimals

Highlighted
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

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

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.

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

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

Meteoroid

Hello Guys, 

 

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

Meteor

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

 

Capture4.PNGUsed Multi Field Formula Tool to get the results.

Meteoroid

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.

Meteor

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.

Meteoroid

This is very helpful! Thanks a lot!

Labels