Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

If OR Statement with FindString

Highlighted
5 - Atom

Hi there!

 

I have a dataset of 56000 rows. The first column of the data set is a text string, , for example:

 

XXX

XYZ-XXX-ABC

XXX-LMNO-AP

LMNOxxx-AP1

4YZPXXX-AC

ABC-DEF

DEF-LMNO-QRT

 

 

 

I want to search the first column for anything containing XXX (XXX- or -XXX or XXX or -XXX-) and if the string is found I want to populate a new column saying "XXX"

 

I then want to do this for a number of other string (for example, DEF)

 

I haven't had much luck using the if contains function and cannot figure out how to do an if then with getstring.

 

Any ideas? Thanks in advance!

 

Highlighted
Alteryx
Alteryx

Set the new column name to whatever you want

 

Your formula will look something like this

 

IF Contains([Column1],"XXX")

THEN "XXX"

ELSEIF Contains([Column1],"DEF")

THEN "DEF"

ELSE Null()

ENDIF

5 - Atom

Thanks Brandon this worked! When I tried this earlier the output was 0 for all rows - maybe my syntax was wrong. Appreciate the quick response!

Highlighted
Alteryx
Alteryx

Could have been a syntax error, but glad that it worked for you! I always click on the fx on the left hand side of the formula box if I ever have a question on syntax because it can auto populate the formula for you if you click it. 

Highlighted
14 - Magnetar

Hi @emk2034,

 

I wouldn't go with a Formula and Contains-Method if you plan to do this with many values. I would go with a Find Replace Tool and an extra list. Here is an example:

grossal_2-1588365051926.png

 

Table 1:

grossal_0-1588365035053.png

 

Table 2:

grossal_4-1588365086551.png

 

 

Output:

grossal_5-1588365174702.png

 

 

 

You could also choose the option to "Case Insensitive Find" if you wan this to happen. I'll attach the example workflow for you. Feel free to check out the different options in the Find Replace tool

 

Let me know if this helped.

 

Best

Alex

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

I would agree that @grossal 's response is a bit more dynamic, but the only thing that neither solution appears to support so far is the idea that the string may contain more than one of your query strings.

 

For example, a string may contain both XXX and DEF, the find and replace tool does not cater for this, nor would the formula method.

 

In this case I would look to do one of either a batch macro (probably more performant), or an append (probably simpler to understand). I've attached an example of the latter. I guess it depends on if this kind of scenario can exist in your data.

 

BenMoss_0-1588403729230.png

 

 

Ben

Labels