community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Filter numbers

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

 

Alteryx Certified Partner
Alteryx Certified Partner

Hi @jonathanzadeh 

 

If you use a Select Tool and convert to a Double type, you'll see that all numbers will be kept and strings will be converted to Null.

Then, you can filter the Null values and you'll be left with numbers.

 

That's just a way to do. There are a lot of other ways, but I think this is a simple one.


Cheers,

Pulsar
Pulsar

Hi @jonathanzadeh 

 

Put this in for your expression in a Filter tool:

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

 

This works for me with your sample data. Test it out on your full data set. 

Alteryx Certified Partner

Hi @jonathanzadeh 

 

This will work:

REGEX_Match([Field1], '\d+|\d+.\d+')

 

Screenshot 2019-05-17 at 13.52.20.png

Alteryx Certified Partner

@BarnesK  @Thableaus  You guys are on the ball today!

This does work. Would you mind explaining this? Would like to understand what i am doing

Pulsar
Pulsar

@jamielaird  I had my Wheaties this morning!

 

giphy (2).gif

Highlighted

Forgot to tag you. @BarnesK 

 

This does work. Would you mind explaining this? Would like to understand what i am doing

Alteryx Certified Partner

The REGEX_Match function looks for pattern matches based on Regular Expressions. See https://regexr.com for more information on these, but in principal we're just using a specific set of commands to look for patterns in the string.

 

In my case the regex statement within the formula is

\d+|\d+.\d+

 

In \d+ \d represents any digit (0-9) and the + after it means ('one or more')

| represents an OR and means that the statement will also be true if the second part of the expression matches

\d+.\d+ is the same as the first part of the expression but adds in a decimal place

 

Hope this helps!

@jamielaird so what is her formula doing. Just hard to follow and i want to really understand this. 

Secondly, how did you get your filter tool to be color coded. Only my formula tool does that

Labels