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

Filter numbers

jonathanzadeh
7 - Meteor

Hi All,

 

I am trying to figure out how i can filter a string column which only contains numbers. For example see below. How can use the filter tool to filter out everything and keep records 1&4? Bare in mind these are in the string data type.

 

Example Column:

1) 0.1333377882

2) N/A

3) N.A.

4) 13336878

 

19 REPLIES 19
jamielaird
14 - Magnetar

@Kenda @Thableaus 

 

True story, yesterday I created a workflow that scraped the history of community posts (sorry community admins!) so I can work out the optimal days and times to hang out on the forum to solve problems.

 

I may blog about this but here's some early insight (times in UTC) - hotter zones are where the most problems are posted to the Designer forum:

Screenshot 2019-05-17 at 14.06.43.png

 

 

 

 

 

 

 

 

 

 

Kenda
16 - Nebula
16 - Nebula

@jonathanzadeh Absolutely!

 

The .* means 0 or more of whatever character and the \d+ means at least one digit (number). Overall, the expression is saying look for at least one digit somewhere in your record. If it finds one, it will come out the true side of the Filter. The reason this works here is because we are under the assumption that your "NA" records will not also contain numbers. 

Thableaus
17 - Castor
17 - Castor

@jamielaird 

 

Wow, impressive. You took this to a higher level!

 

I'm more like @danilang, trying to have fun while I'm learning a lot from different situations.

 

The Alteryx Community is amazing and I really hope to get to know all of the experts at the Inspire Conference this year.

 

Cheers,

danilang
19 - Altair
19 - Altair

@Kenda , @jamielaird@Thableaus 

 

Don't forget the optional minus sign

 

REGEX_Match([Field1], '-{0,1}\d+|-{0,1}\d+.\d+')

 

Dan 

jonathanzadeh
7 - Meteor

@Kenda - if i just had \d+ if would look for any cell with at least one digit right? Does that include 0? Further, what is the need .*? Can't i just had the \d+ and it will automatically find any cell with an integer?

jamielaird
14 - Magnetar

@Thableaus Definitely! I've only really got involved in solving problems on the community in the last couple of months and didn't realise how much fun it is.

 

It's definitely not a competition, and that's just as well because @MarqueeCrew's stats are unassailable :D

 

Screenshot 2019-05-17 at 14.22.05.png

danilang
19 - Altair
19 - Altair

@jamielaird 

 

Good work on the scraping.   And...Wow!  You've really made a splash this month.  With you, @Thableaus and @afv2688 this active, I don't think I'll ever make top contributor again.

 

Dan

 

 

jamielaird
14 - Magnetar

@danilang Don't worry there are plenty of problems to solve!

 

Screenshot 2019-05-17 at 14.27.34.png

Kenda
16 - Nebula
16 - Nebula

@jonathanzadeh 

 

Yes, it would include 0 if you just had \d+. The reason I added the .* is in the case there were any numbers with decimal points. If you only had \d+, it would only keep whole numbers. Technically since the decimal point would come first, you wouldn't need the second .* and could just use the expression 

REGEX_Match([Field1], ".*\d+")

 

 

Yet another option would be 

REGEX_Match([Field1], "\d*\.?\d+")

 

Which is saying look for 0 or more digits then an optional decimal point (we are telling Alteryx that it is optional by putting the question mark after it and we have to put the \ before it to tell Alteryx that it is looking specifically for the decimal point and not just any character) finally followed by at least one digit.

 

It all depends on how specific you want to get and what your data looks like! :) 

someotherguy
8 - Asteroid

@jonathanzadeh the regex is matching to any whole number(\d+) or(|) any decimal number (\d+.\d+)

 

the website www.regexr.com does a good job breaking it down further, if you paste the expression in the top

someotherguy_0-1661442494463.png

 

In my earlier attempts at dealing with string and numbers in the same field i tried to test if the field was a number by converting it to a number but that doesn't work

someotherguy_1-1661442687808.png

 

Labels