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.
Solved! Go to Solution.
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
thank you you are a superstar ༼ つ ◕_ ◕ ༽つ★★★★★
thank you you are a superstar ༼ つ ◕_ ◕ ༽つ★★★★★
@NicoleJohnson
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')
@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!!
༼ つ ◕_ ◕ ༽つ☆☆☆☆☆ stars for you too
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
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!
Thanks! Perfect Solve! :)