Formatting number with commas and decimals
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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! :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello Guys,
can you share me the script of screen shot so that i can use it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi, here is the approach I tried to get the desired results.
Used Multi Field Formula Tool to get the results.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This is very helpful! Thanks a lot!
