Filter numbers

[jonathanzadeh]

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

[Thableaus · Accepted solution]

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,

[Kenda]

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. 

[jamielaird]

Hi @jonathanzadeh 

This will work:

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

[jamielaird]

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

[jonathanzadeh]

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

[Kenda]

@jamielaird  I had my Wheaties this morning!

[jonathanzadeh]

Forgot to tag you. @Kenda 

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

[jamielaird]

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!

[jonathanzadeh]

@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