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

Wildcards in Conditional Formulae

DHB
8 - Asteroid

I'd like to create a column of values based on whether a certain string appears in another field.

 

 

In the example below I'd like to return a value of 'SEVENTH' if the value in the code column contains '_7', 'FIRST' if it contains '_1' and 'SECOND' if it contains '_2'

codeNEW COLUMN
3117_CORE_7099SEVENTH
 3007_CORE_1099FIRST
 1457_COMP_2099SECOND

 

I'm not make any progress with this at the moment.  Can anyone help?

 

Thank you in advance.

 

Best,

 

DHB

4 REPLIES 4
NicoleJohnson
ACE Emeritus
ACE Emeritus

I believe your best bet in this case would be to isolate just that first digit after the second underscore (I used RegEx in the attached example, but there are other string functions you could use in a formula tool such as Substring, or you could use Text to Columns tool based on underscore delimiter and then extract just the first number from the third column created, etc.). Once that number is isolated, you can use a Find & Replace tool to match the number to a set table that shows 1 = FIRST, 2 = SECOND, etc. See attached for an example using the sample data provided.

 

Does that achieve what you're looking for? Would there ever be a case where it would need to go higher than NINTH?

 

Cheers,

NJ

DHB
8 - Asteroid

Thank you Nicole,

 

I've had a bit of a breakthrough here with this formula...

 

IF Contains([COURSE_CODE],"_1") THEN "First" elseif Contains([COURSE_CODE],"_2") THEN "Second" elseif Contains([COURSE_CODE],"_3") THEN "Third" elseif Contains([COURSE_CODE],"_4") THEN "Hons/Fourth" elseif Contains([COURSE_CODE],"_5") THEN "Hons/Fifth" elseif Contains([COURSE_CODE],"_6") THEN "Hons/Sixth" elseif Contains([COURSE_CODE],"_7") THEN "Masters" else null() endif

 

I hadn't seen the IF Contains before but it seems to work.

NicoleJohnson
ACE Emeritus
ACE Emeritus

There is definitely more than one way to solve this problem, and the formula approach will certainly work! 

 

There's another formula option that might be a bit cleaner looking, assuming that the course code always follows the same format of ####_AAAA_#### (or if it doesn't, there are ways around that too...):

 

Switch(Substring([Course_Code],10,1),"N/A","1","First","2","Second","3","Third","4","Hons/Fourth","5","Hons/Fifth","6","Hons/Sixth","7","Masters")

 

The Switch formula basically gives you a simpler Case statement than a whole bunch of nested IF statements. At any rate, there are probably a dozen different ways to skin this cat! :) Glad you were able to find a solution that worked!

 

NJ

DHB
8 - Asteroid

That's great, thank you so much Nicole.  Much cleaner than my formula

Labels