Remove negative from left to right?

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)

Transformation

Accepted answers

atcodedog05

Hi @Hamder83

Here is how you can do it.

Workflow:

Hope this helps : )

negative.yxmd

All comments

atcodedog05

Hi @Hamder83

Here is how you can do it.

Workflow:

Hope this helps : )

negative.yxmd

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

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.

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?

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335