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 Knowledge Base

Definitive answers from Designer Desktop experts.

Filter Using String Data

PaulT
Alteryx Alumni (Retired)
Created

There are a handful of ways to search for a particular string within a data field.  If you want to perform a query, identifying records with a particular string field within a data field:

  1. Use the Filter tool: the result will be two streams - those records that meet your filter criteria and those that do not.
  2. On the Functions Tab, expand the string tree and select FINDSTRING(String, Target)
  3. Replace the `String` parameter with the field name
  4. Replace the `Target` parameter with the value you are looking to identify
  5. Finish the expression with !=-1 which will separate the true values from the false ones.
  • Example - If you are trying to identify all of the customers with Joe in a data field [Name] :
    • FINDSTRING([Name], "Joe")!=-1
    • The records that meet this criteria will be output from the True anchor ([Name] contains the value "Joe"). All other records will be output from the False anchor.


This function can also be used in the Formula tool; if for example you want to populate a different data column based on the [Name] field, you can use the FINDSTRING in an IF statement.  Example: You would like to classify your data in a new field based on the instance of customers with Joe in a data field [Name] :
 

IF (FINDSTRING([Name], "Joe") != -1) THEN "JOE Customer" ELSE "Other" ENDIF
 

  • This will populate a new data field with "JOE Customer" if the field "Name" contains "Joe" otherwise it will populate that field with the value "Other"
  1. In the Formula tool, add a new field by selecting + Add Column, or choose an existing field to update.
  2. Make sure the appropriate Field Type and Size is also specified
  3. On the Functions Tab, expand the Conditional Tree and select IF c THEN t ELSE f ENDIF
  4. On the Functions Tab, expand the String tree and select FINDSTRING(String, Target) to replace c
  5. Replace `String` with the field name
  6. Replace `Target` with the string you are looking to identify
  7. Finish this part of the expression with !=-1 which will separate the true values from the false ones
  8. Replace "t" with the desired value to populate the new field if the condition is met: "JOE Customer"
  9. Replace "f" with the desired value to populate the new field if the condition is not met: "Other"
Comments
brad_j_crep
8 - Asteroid

Is there a way of making the first FindString usage case insensitive?  If I was looking for "Joe" but typed in "joe" what would allow me to still find "Joe?"

 

Thanks,

PaulT
Alteryx Alumni (Retired)

Hey Brad,

 

There are two things that can be done in your case. The first would be to use the Find Replace tool which has an option for Case Insensitive Finds. The other option is more of a data prep best practice, and that is to set all of your fields to one case (either Upper case or Lower case). You can do this in a Formula tool, and set all of your expressions in that manner, or you can account for it in the expression you're building.

 

For example:

 

IF (FINDSTRING(UpperCase([Name]), "Joe") != -1) THEN "JOE Customer" ELSE "Other" ENDIF

 

I simply took the sample expression from above, then added an additional function to make sure that the expression reads the NAME Field as Uppercase when executing the expression. In this particular case you would expect this particular expression to categorize "Joe" as "Other" as JOE does not equal Joe.

 

Hope that helps!

aiyer1
6 - Meteoroid

Thanks for this explanation 

 

Can the FindString also be used in the case where I would like to get all the rows below a text ?

 

For example - in a text file, if I have the following structure - 

 

[Metadata]

+

[a key word]

[valid data]

 

Can I use the Filter tool to extract all the rows from this file as soon as Alteryx finds the 'key word'?

If not, please let me know how this can be achieved.

 

cheers

Ananth

PaulT
Alteryx Alumni (Retired)

Hi Ananth,

 

I'm having a little trouble conceptualizing what your data looks like. Do you have a sample of what you have and what you'd like to see?

 

Specifically, are you saying you have a text file with no inherent delimiter? or is the + sign your delimiter? I'm sure we can figure something out in this scenario but am not convinced that the process will be as simple as a single expression.

 

Thanks,

 

Paul

Andy_Katona
8 - Asteroid

I am in the same boat & trying a couple different things. My string I need to pull the region they are in but they are buried in the string. Say: COLUMNA: "LAB_ADDHOC_CENTRAL_V17ROOT". I need to pull on "CENTRAL" (EAST, WEST, SE1, etc..) to create load files.

 

I have read through many different posts & can't seem to narrow it down. I have tried the filter, formula, RegEx, text to column...without success. I am trying the FINDSTRING now.

 

Are things able to be found with wildcards? What are the wild cards? I have tried * & % but it didn't work. I know I am missing something simple

MarqueeCrew
20 - Arcturus
20 - Arcturus

Use a filter:

 

contains[field],"central") or

contains[field],"east") or

contains[field],"west") or

contains[field],"north") or

contains[field],"south") 

Andy_Katona
8 - Asteroid

Again, I am over thinking it. I was able to utilize CONTAIN({PROJECT_NAM}, "CENTRAL") to return a true. I kept wanting to use wildcards & didn't need to. WOW.

 

I just ended up trying the following AlteryX link for the contains function. https://help.alteryx.com/current/index.htm#Reference/Functions.htm#String

 

Seemed to simple & straight forward. :-)

NotQuiteClueless
7 - Meteor

Hi, I have some data where permitted string values take the form of the letters 'RG' followed by five numerals. Is there an efficient way to screen out anything that doesn't conform to the format?

patrick_digan
17 - Castor
17 - Castor

@NotQuiteClueless I would use Regex in a filter tool: 

Regex_Match([Field1],"RG\d{5}")

  

NotQuiteClueless
7 - Meteor

Thanks! I did find a workaround but that's much more elegant. Allows me to dip my toe in the Regex pool too ...

aiyer1
6 - Meteoroid

thanks for the comments folks. I managed to use Python in this case to loop through the files in a directory and read the files line by line and based on a text match, perform further actions...similar to what many of you have suggested. 

sangeethadetne
7 - Meteor

@PaulT wrote:

There are a handful of ways to search for a particular string within a data field.  If you want to perform a query, identifying records with a particular string field within a data field:

  1. Use the Filter tool: the result will be two streams - those records that meet your filter criteria and those that do not.
  2. On the Functions Tab, expand the string tree and select FINDSTRING(String, Target)
  3. Replace the `String` parameter with the field name
  4. Replace the `Target` parameter with the value you are looking to identify
  5. Finish the expression with !=-1 which will separate the true values from the false ones.
  • Example - If you are trying to identify all of the customers with Joe in a data field [Name] :
    • FINDSTRING([Name], "Joe")!=-1
    • The records that meet this criteria will be output from the True anchor ([Name] contains the value "Joe"). All other records will be output from the False anchor.


This function can also be used in the Formula tool; if for example you want to populate a different data column based on the [Name] field, you can use the FINDSTRING in an IF statement.  Example: You would like to classify your data in a new field based on the instance of customers with Joe in a data field [Name] :
 

IF (FINDSTRING([Name], "Joe") != -1) THEN "JOE Customer" ELSE "Other" ENDIF
 

  • This will populate a new data field with "JOE Customer" if the field "Name" contains "Joe" otherwise it will populate that field with the value "Other"
  1. In the Formula tool, add a new field by selecting + Add Column, or choose an existing field to update.
  2. Make sure the appropriate Field Type and Size is also specified
  3. On the Functions Tab, expand the Conditional Tree and select IF c THEN t ELSE f ENDIF
  4. On the Functions Tab, expand the String tree and select FINDSTRING(String, Target) to replace c
  5. Replace `String` with the field name
  6. Replace `Target` with the string you are looking to identify
  7. Finish this part of the expression with !=-1 which will separate the true values from the false ones
  8. Replace "t" with the desired value to populate the new field if the condition is met: "JOE Customer"
  9. Replace "f" with the desired value to populate the new field if the condition is not met: "Other"


Hello all, 

 

I went through the steps and tried to use the FINDSTRING method but i have no records fetched, but the records exists in the data. pls find the screenshot. I could have used CONTAINS METHOD that would work but the i am facing error using the FINDSTRING . Pls correct me if I have  done something wrong in the process.

 

sangeethadetne_0-1590718434714.png

 

sangeethadetne_1-1590718463126.png

 

sangeethadetne_2-1590718494143.png