Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to convert a number to a negative number when the minus sign suffixed?

Anamika
6 - Meteoroid

Hi,

 

I have values that has the negative sign as a suffix instead of a pre-fix. E.g. "100 -" instead of "-100". How can I convert this to (100)? Need help with this.

14 REPLIES 14
MarqueeCrew
20 - Arcturus
20 - Arcturus

Hi @Anamika,

 

Here's an approach for you to try:

 

Regex_Replace([Field1],"(\d+)(.*)",'$2,$1')

It will swap things for you.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Anamika
6 - Meteoroid

Thanks but i forgot to mention that my number is stored as a string...hence, your earlier solution is not working.

MarqueeCrew
20 - Arcturus
20 - Arcturus

What exactly is not working?  It should operate on strings (not numbers).  Did you change [Field1] to your input field?

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Anamika
6 - Meteoroid

Yes i did that. So the "100-" is now showing as ",00-,1"

MarqueeCrew
20 - Arcturus
20 - Arcturus

My apologies @Anamika ,

 

My configuration included a comma.....

 

Regex_Replace([Field1],"(\d+)(.*)",'$2$1')

All should be better now.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
DavidP
17 - Castor
17 - Castor

How about a simple idea like this?

 

negative number.png

DavidP
17 - Castor
17 - Castor

Sorry, you'll need to put an ABS in there as well, like this:

 

if Contains([Field1], '-') then '('+tostring(abs(tonumber([Field1])))+')' else [Field1] endif

MarqueeCrew
20 - Arcturus
20 - Arcturus

@DavidP,

 

Does that really simplify life?  

 

If Right([Field1], 1) = '-'
Then '-'+ Trim([Field1],'-')
Else [Field1]
Endif

If you really don't like RegEx.....

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
DavidP
17 - Castor
17 - Castor

Didn't mean to cause offence Mark.

 

The regex does a great job of flipping the minus sign to the front, but the desired outcome is to identify a negative number and display it as (100).

Labels