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

Number formatting

jannis2021
6 - Meteoroid

Hi -
I am wondering if its possible to set up a generic multifield expression to fix common number formatting issues. Ideally it would handle several types of issues (i.e. parenthesis depicting negatives, trailing negatives, commas, etc. even if not all those issues are present. See the expression below for an example of what I am going for. Currently the results all show zero as none of my sample data have all the issues the expression is attempting to address. Any thoughts? Thanks.

 

Replace([_CurrentField_],",","")
AND
Replace([_CurrentField_],"(","-")
AND
Replace([_CurrentField_],")","")
AND
Regex_Replace([_CurrentField_],"(.*)\-$","-$1")

 

 

Sample #Expected Result

8,000.00

8000.00

(8,000)

-8000.00

8,000-

-8000.00
13 REPLIES 13
JoaoLeiteV
10 - Fireball

Hey there,

 

Instead of an "AND" Condition, have you tried using "OR"?

 

Edit: You could also try using all of the formulas in a single one: Regex_Replace(Replace(Replace(Replace([Sample #],",",""),"(","-"),")",""),"(.*)\-$","-$1")

Jean-Balteryx
16 - Nebula
16 - Nebula

Hi @jannis2021 ,

 

The problem comes from using AND keyword. Using it, you are testing your field based on your expression and it returns à to indicate it's false.

 

What you need to do is chain your expressions :

 

Replace(Replace(Replace(Regex_Replace([_CurrentField_],"(.*)\-$","-$1"),")",""),"(","-"),",","")
MichaelSu
Alteryx Alumni (Retired)

Hi @jannis2021 ,

 

In addition to @Jean-Balteryx  and @JoaoLeiteV solutions provided above, you might also find the number cleansing macro on the Alteryx Gallery helpful. @Treyson built this a while back but it may help to automate some of the formatting you are looking to do. 

 

https://gallery.alteryx.com/#!app/Number-Cleansing-Macro/5b6df1870462d71090803ac9

 

MichaelSu_0-1626710091011.png

 

 

Thanks,

Mike

Treyson
13 - Pulsar
13 - Pulsar

@MichaelSu that's a blast from the past! I had to open it back up to check. it does account for parenthesis and allows for folks that use commas instead of periods for the radix character to select that. 

 

Also fun thing the radix character is that division mark between full numbers and decimals. That's what I remember from making this macro. Learning that.

Treyson Marks
Senior Analytics Engineer
MichaelSu
Alteryx Alumni (Retired)

@Treyson  some things are just timeless...Wonderwall...Mr. Brightside...Treyson's macro!

 

Definitely a good one!

apathetichell
18 - Pollux

@MichaelSu is @Treyson  Liam or Noel?

Treyson
13 - Pulsar
13 - Pulsar

I think Treyson1 is Treyson. Let me confirm.

Treyson Marks
Senior Analytics Engineer
Treyson
13 - Pulsar
13 - Pulsar

Confirmed, it’s this guy:

 

https://www.linkedin.com/in/dynamicinput

Treyson Marks
Senior Analytics Engineer
apathetichell
18 - Pollux

@Treysonmusic reference based upon @MichaelSu 's comment... but I think your music tastes and mine may converge and stray fairly far from Oasis...

 

Labels