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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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.

10 REPLIES 10
NicoleJohnson
ACE Emeritus
ACE Emeritus

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
ACE Emeritus
ACE Emeritus

@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