Alteryx designer Discussions

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

How do I write the formula to look for more than one value in a text string

Highlighted
8 - Asteroid

Not finding this answer in the discussions...

 

How do I write the formula to use in the formula tool to look for more than one value in a string with in an IF AND statement?

 

This is what I have:

[MP]="HC" && [ADJ]!=0 && [PrevA]=0 && [PAYS]!=0 && CONTAINS([DESC],"TIME")
then ...

 

What I need is to look for "TIME" OR "DISTANCE" in the [DESC] field...

Highlighted
Alteryx Certified Partner

You'd need to change your formula to:

 

IF [MP]="HC" && [ADJ]!=0 && [PrevA]=0 && [PAYS]!=0 && CONTAINS([DESC],"TIME")

OR [MP]="HC" && [ADJ]!=0 && [PrevA]=0 && [PAYS]!=0 && CONTAINS([DESC],"DISTANCE")

THEN X

ELSE X

ENDIF

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Regards,

Jonathan

 

Highlighted
Alteryx
Alteryx

@sonseeahray 

 

Good question. The formula below will identify whether or not the field contains both "TIME" and "DISTANCE".

If contains([DESC], "TIME") AND contains([DESC], "DISTANCE")
Then "True"
Else "False"
Endif

 

If you would like to determine whether or not it has at least one of the two, the following formula will work.

If contains([DESC], "TIME") OR contains([DESC], "DISTANCE")
Then "True"
Else "False"
Endif

 

Let me know if there are any questions. If I misunderstood the task, please let me know and I will provide additional assistance. 

Highlighted
17 - Castor
17 - Castor

Hi @sonseeahray 

 

Shorter than @Jonathan-Sherman's version, but logically equivalent.  Note the extra brackets in bold.  

 

IF [MP]="HC" && [ADJ]!=0 && [PrevA]=0 && [PAYS]!=0 && ( CONTAINS([DESC],"TIME") OR CONTAINS([DESC],"DISTANCE") )

THEN X

ELSE X

ENDIF

 

Dan

Highlighted
Alteryx Certified Partner

I did wonder about brackets but have never seen them used! Thanks @danilang, learn something new everyday!

Highlighted
8 - Asteroid

Thank you @danilang! That is what I needed!

Labels