Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

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

Highlighted
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.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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 reboot. Order shall return.
Highlighted
6 - Meteoroid

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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 reboot. Order shall return.
Highlighted
6 - Meteoroid

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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 reboot. Order shall return.
Highlighted
16 - Nebula
16 - Nebula

How about a simple idea like this?

 

negative number.png

Highlighted
16 - Nebula
16 - Nebula

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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 reboot. Order shall return.
Highlighted
16 - Nebula
16 - Nebula

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