Hi
I have a number like this;:
Discount amount
4.051,03-
That im trying to make a number like this:
-4.051,03
When I use my furmula it removes the last two digits. And I cant find a way to solve it?
Formula:
ToNumber(IF Right(Trim([field8]),1) = '-'
THEN ToNumber(Left(Trim([field8]),Length(Trim([field8]))-1))*-1
ELSE Trim(field8)
ENDIF)
Solved! Go to Solution.
hi @Hamder83
You almost got it right. The reason why the last 2 digits are missing because the decimal separator is not specified, hence the digits after "," is ignored. Find out more here at: ttps://help.alteryx.com/designer-cloud/conversion-functions
Dawn.
hi @atcodedog05
Thanks for the suggestion 🙂
As I see it on your picture it returns a whole number? : -4.05103
And not -4.051,03 ? 🙂
Hi @atcodedog05
I have to be the worst alteryx worker ever.
Your formula works, and as a string it shows correctly.
But no matter what settings i use, i cant convert it to a numeric field and keep the last two digits?
Both with and without
hi @Hamder83
Actually @atcodedog05 workflow already converted to string to the format that you need. When you use the Select Tool to convert from String to Double, it does not read the value after the comma decimal separator.
In this case, you can use the ToNumber function (https://help.alteryx.com/20213/designer/conversion-functions). Note that there is a bug that was fixed from v 2020.4 onwards.
Examples
ToNumber("878") returns the string 878 as a number.
ToNumber("4.256411411E9") returns the string 4256411411 as a number.
ToNumber("Number", "false") returns 0 with conversion error: TONUMBER: Number lost information in conversion.
ToNumber("Number", 0, 0) returns 0 with conversion error: TONUMBER: Number lost information in conversion.
ToNumber("Number", 1, 0) returns 0 with no conversion error.
ToNumber("Number", 1, 1) returns [Null] and no conversion error.
ToNumber("123456,789", 1, 1, ",") returns 123456.789 as a number.
ToNumber("123.456,789", 1, 1, ",") returns 123456.789 as a number. This is because the period is automatically interpreted as the thousands separator, while the comma is specified as the decimal separator via decimalSeparator.
Alteryx Help document can be very useful as you start your learning journey, highly encourage that you check it out together with the other resources in the Alteryx Academy.
Dawn.
Hi @Hamder83
Number with comma is not a supported format in Alteryx.
Either you get number without comma or with comma but is a string.
Hope this helps : )
So to convert it into a US style number
Would I have to make it 4051.03 ?
Because I have another stream with amounts: 1.7 and so on, that I as normal can convert into a number with commas:
using:
But after changing the coloumn from text to double it converts the negative number to a whole number?