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

Alteryx designer Discussions

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

Filtering Question

Asteroid

I have a column of data that contains a bunch of messy data (Account Numbers).  I want to filter on fields that contain numbers and/or letters - they are account numbers generally 7 to 9 digits long.  There is other data in the Account Numbers column that have letters that I don't want want.  The field is set as a "string" which I am not sure if that is right.  They have specific characters (* : -).  If it's easier, I can just filter out the data that contains special characters.  Is there a way to filter them all at once, or do I need to do separate filters?

Alteryx Certified Partner
Alteryx Certified Partner

@bcrisman

 

The best way to get an answer would be to provide some sample data. In this case, there are  a few way to go about removing special characters.Do you only want to remove special character and leave anything alphanumeric, or are account numbers only numeric and you want to remove anything that isn't a numeric character? Use of the RegEx tool will probably be the best bet in any case.

 

I've attached an example that used the RegEx tool to replace any non-alphanumeric character with nothing. 

 

 

Quasar
Quasar

Hello @bcrisman I agree with @CharlieS that sample data will help in such cases.

 

However, going by the problem statement, it looks like RegEx tool is the solution.

 

I have attached solution of two weekly challenges (4 & 11) where RegEx too has been used and solution for 11 is more related to yours.

 

Hope this helps!

Asteroid

Thanks Charlie.  Here's an example.  I want to remove anything that doesn't have an account number.  Account numbers contain both numbers and letters.

 ------------------------------------------------------------------------------------------------------------------------------------
 |Companies: 
 |Profit Centers: 
 |Accounts:                                                           
 |Period: 
 ------------------------------------------------------------------------------------------------------------------------------------
  
 Account
        G1050011  S/T Cash Types
 ***    Short Term Investments
 ****   Invested Assets
        1100218   C Inv Cash / BONY
        1101710   F BADI AP
 

       1101911   F AP DbACH Out

Alteryx Certified Partner
Alteryx Certified Partner

It looks like the indentation on the account lines is the same every time, is that going to be the case for all scenarios? Perhaps you could use this to select the relevent rows.

 

Something like a filter tool with the value...

 

left([Field],5) = "      "

 

Ben

 

 

Asteroid

I was able to trim all the whitespace.  The data I copied was from the original source.  Thanks though.

Pulsar

I think what @BenMoss was referring to wasn't how to trim the whitespace, but using the whitespace to determine which lines to keep.  In the sample you gave, it looks like every line with an account number has a number of spaces at the beginning.  With a Filter tool, you can add the formula he supplied and get just the records with the leading whitespace, and then trim.  Does that make sense?  See attached for an example.

 

Can you give an example of what the output would look like?  Are you looking for just the account numbers, or do you want the descriptions that follow as well?

 

Alteryx Certified Partner
Alteryx Certified Partner

Thanks @danrh, you are exactly right.

Asteroid

I think that worked.  Thank you all!

Labels