Remove negative from left to right?

[Hamder83]

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)

[atcodedog05 · Accepted solution]

Hi @Hamder83 

Here is how you can do it.

Workflow:

Hope this helps : )

[DawnDuong]

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.

[Hamder83]

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 ? 🙂 

[atcodedog05]

Hi @Hamder83 

Here is the updated workflow

Hope this helps : )

[Hamder83]

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 

[DawnDuong]

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.

[atcodedog05]

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

[Hamder83]

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?