ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

Using a wildcard to capture multiple items in a field

RCS
7 - Meteor

Hi alteryx community,

 

Im having an issue writing an if statement using wildards. 

 

Lets say I have field entries with wave 1, wave 2, wave 3, wave etc.

 

If [Other] = "Wave*"

Then 1

Else 0

Endif

 

Im trying to use a wildcard or any other related function to capture anything that starts with "Wave" and end in a plethora of variable endings.

NicoleJohnson
14 - Magnetar
14 - Magnetar

Could you use the formula Contains?

 

If Contains([Other],"Wave") Then 1 Else 0 Endif

 

...Alternatively, if Wave should always be the first word, you could use:

 

If Left([Other],4) = "Wave" Then 1 Else 0 Endif

 

... or play with RegEx!

 

RegEx_Match([Other],"Wave\s.*") <-- This should give you a 1 for a match without having to put it in an IF statement

 

Hopefully one of those does the trick! 🙂

 

NJ

RCS
7 - Meteor

thank you you are a superstar ༼ つ ◕_ ◕ ༽つ

RCS
7 - Meteor

thank you you are a superstar ༼ つ ◕_ ◕ ༽つ★★


@NicoleJohnson
Claje
14 - Magnetar

I wanted to add on to Nicole's excellent answer by copying a post I made the other day regarding the "LIKE" keyword from languages like SQL, that I think can be helpful here.

 

In particular, the STARTSWITH() and ENDSWITH() functions are both really powerful and more flexible than LEFT([field],length,'searchstring') or RIGHT([field],length,'searchstring')

 

I'm going to use examples with a SQL syntax for comparison to give you all three versions of the "Like Operator" as I would recommend in Alteryx

 

LIKE 'abc-treatment%'  (start of string):

STARTSWITH([field],'abc-treatment')

LIKE '%abc-treatment%' (any part of string):

CONTAINS([field],'abc-treatment')

LIKE '%abc-treatment' (end of string):

ENDSWITH([field],'abc-treatment')

 

 

NicoleJohnson
14 - Magnetar
14 - Magnetar

@Claje - This is a SUPER helpful comparison, would give it 3 stars if I could 🙂 I will definitely be using this in the future! Thanks for excellent tip!!

RCS
7 - Meteor

༼ つ ◕_ ◕ ༽つ☆ stars for you too

GaryKennedy
6 - Meteoroid

Hi Claje,

 

I am new to alteryx so apologies if this is a basic question.

 

How would you do the opposite for example:

 

[Field] NOT LIKE ('%reject%')
[Field] NOT LIKE  ('%deleted%')
[Field] NOT LIKE ('%Cancel%%')

 

Is there a way of excluding with the % wildcard?

 

Regards,

Gary 

Claje
14 - Magnetar

Hi,

 

in the examples you provided I would use !CONTAINS()

 

here are some examples:

!CONTAINS([Field],'reject')

!CONTAINS([Field],'deleted')
!CONTAINS([Field],'Cancel')

 

CONTAINS() will return TRUE if the string is found in a field.  the exclamation point/bang (!) reverses this statement, so if the string is found, !CONTAINS() will return FALSE.

 

This is basically the same as saying DOES NOT CONTAIN.

 

Hope this helps!

GaryKennedy
6 - Meteoroid

Thanks! Perfect Solve! 🙂 

Labels