Converting strings to numbers after a decimal

[HTWebb]

I would like to perform a calculation that will result in a number that has several decimal places such as 15.06521

However I need to truncate the number at 2 decimal places WITHOUT rounding. I would like the number to be 15.06

Also if I am exporting this to someone who may change the format should I save it as a TEXT field after I truncate?

[mborriero]

I would  use a Regex tool using this expression [0-9]*\.[0-9][0-9]

Find workflow attached.

[MarqueeCrew]

This may be overly complicated, but:

ToNumber(regex_replace(tostring([Field1]),"(\d*\.\d{2})\d*",'$1'))

will create a NUMBER Data Type

or

regex_replace(tostring([Field1]),"(\d*\.\d{2})\d*",'$1')

a string.

Cheers,

Mark

[patrick_digan]

@HTWebb If you wanted a non-regex option, you could have a formula tool that creates a new string field with something like this:

left(ToString([Field1],10),length(ToString([Field1],10))-8)

It converts it to a string and rounds to 10 characters, then takes off the last 8 since those are just extra decimals if you only want 2 decimals. I just used 10 since it seems like a big enough number. If you indeed wanted a number, you would just need to a tonumber().

[jonathandrummey]

[edited 2017-07-13 - I've got a better solution further down in the thread]

In other tools rather than doing string manipulation*** I use a formula like INT(FLOAT([raw string])*100)/100. From the inside out this converts the string to a floating point number, multiplies that by 100 (i.e. turning the two decimal places into an integer value), uses INT() to truncate the number, and then divides by 100 to convert back to two decimal places.

*** the reason I avoid string manipulation is partially performance, partially for easily handling non-US data sets where commas are used for marking the decimal place. I'm all in favor of making the OS and the underlying app do all the work that it can rather than me having to customize my code.

Since Alteryx doesn't have an equivalent to an INT() function (that I've ever found) I used a combination of the following:

1. Created a "Converted Number" with the formula ToNumber([raw string])/100

2. Created an "Output Number" with the formula IF [Converted Number] >= 0 THEN FLOOR([Converted Number]) ELSE CEIL([Converted Number]) ENDIF/100. This uses the FLOOR() and CEILING() functions to mimic the results of an INT().

The workflow is attached.

I'm not sure about your last question about someone changing the format...I'd say it depends on what they want to do with the values, for example if they are going to be adding up values then sticking with numbers is easier, same if they are non-US where they'd naturally want a different number format, if they are using raw values in tools like Excel and Tableau then numbers are naturally formatted in those tools to be right-aligned which is easier to read, if they are text then the default is left aligned which would need to be manually corrected, etc.

Jonathan

[patrick_digan]

@jonathandrummey very interesting, i like your approach! 

Perhaps you can solve this problem which I'm not sure I have an elegant answer to yet. When I use your workflow and modify it so that the number is coming from a formula, there is an issue with the way alteryx stores numbers in v11.0+. I'm attaching an example where my formula is 2.28/2 which obviously equals 1.14. Then when I run it through your formula, it results in 1.13. Note that I think this is only an issue in v11.0+. Alteryx essentially is storing a different number in the background than what it displays (ie it displays 1.14 but stores 1.1399955843512 or something). 

I'm just curious of your thoughts

[jonathandrummey]

Hi Patrick,

I'm suspect it's a variation on a problem that other computer applications have, it's that some numbers don't have a binary representation that can fit into 8 bytes so there is rounding involved that (most of the time) is invisible to us. I can get the problem you describe just by using 1.14 or 1.16 as input.

I'll take a look, a couple of weeks ago I was dealing with this in Tableau and looking at various alternatives and trying to find something that didn't involve string manipulation (which can be really slow in Tableau compared to using numbers). I've got a data set I was using for that that I can try out.

Jonathan

[jonathandrummey]

Here's a revised formula that doesn't have the problem of my original, and meets my original goal of doing the the rounding/truncation as math rather than string manipulation/regex:

FLOOR(ABS(ToNumber([Raw String])*100)+.000000000001)/100 * IF ToNumber([Raw String]) >=0 THEN 1 ELSE -1 ENDIF

What it does is:

1) convert the string to an number

2) multiply that by 100

3) use ABS() to turn that into a positive value

4) add a tiny number to get around binary number representation problems

5) round that down using FLOOR()

6) multiply that by +/-1 to get the sign back

Workflow is attached, there are ~40 different test cases for known problematic numbers like 1.14 and 1.16, rounding around 0.5, negative numbers, numbers really close to zero like .9999999999, etc.

Jonathan

[HTWebb]

Thank you all, I was able to get exactly what I wanted. Speical thanks to jonathandrummey for the solution I used. I was able to make it a fixed 5.2 format as well which I need for my deliverable!

Thanks again. It was a way easier solution than I had originally used.

[krishcode0]

hi marq,

i have string as ($2029.09) , i want to convert it to 2029.09 , could you please help.

Thanks,Krishna