Filter Rows with specific data

[jseaburn]

Hello - I am looking for some help figuring this out.  I imagine it may be REGEX (which I'm not great at) or a custom filter, but I'm not clear exactly how to do it.  I only want rows of data that start with the "Axxxxxxx" in the Cost Element field.  I want to get rid of all rows that do not start this way.

I've attached an illustration showing the rows I want to keep.

Thanks for your help!

[Blake · Accepted solution]

Hi @jseaburn 

Check out the attached workflow and let me know if it works for you. I've included an example using regex within the filter tool and using the regex tool. Either one works and uses the same expression. The expression is saying that the field needs to start with "A" followed by at least 7 digits and then anything else. i.e. if there are 10 digits it is ok, if there are only 6 digits, it won't match.  

[jseaburn]

@Blake 

That did it! I was close on my expression, but not close enough.  And I also tried to use "Parse" thinking I needed to parse out that beginning account code.  I'm going to need to educate myself a little better on these Regex functions.  Thanks for the quick help!  I appreciate it!

[jseaburn]

@Blake

I did notice a slight difference in the output.  Using the regex_match within the filter tool resulted in one of the rows being labeled as False, yet using the REGEX tool, it was true.  It was the record starting with lowercase A.  My data will always have an uppercase A so this wouldn't have affected my results, but in your example, it was something I noticed and am curious about.  Can the custom filter expression be modified to allow for both cases of A?

Thanks again!

[Thableaus]

@jseaburn 

You need to uncheck the "Case Insensitive" box using the REGEX tool, per @Blake's solution.

Cheers,

[Blake]

Hey @jseaburn - ahhhh my bad, I accidentally left the case insensitive box checked on the regex tool so it wouldn't matter if it was an "a" or and "A" 

On the filter tool I did add that I wanted it to be case sensitive by including ",0" at the end of the expression. 

If you don't care about case sensitivity, remove the ",0" from the filter tool and keep the box checked in the regex tool. If you are sensitive to casing, uncheck the box on the regex tool or leave the regex match expression in the filter tool the same. 

Sorry about the confusion! 

If you want a walk through of the Regex tool, AB Data has a youtube video you can check out. 

There are also some sites like regexr.com and regex101.com which are great resources for learning Regular Expressions. 

Thanks and good luck! 

[jseaburn]

Awesome!  Thanks for all the suggestions.  Have a great weekend!

-Jeralee