How to match string with wildcard (*)

[hcho]

I currently have a workflow that ensures a field matches one of the specified classification categories (referred to as "Classes") and flags those that do not exactly match the selected categories.

For example, my requirements file requests that all "Occupations" other than "Doctor" and "Software" be returned:

FileName	Field	Type	Classes	EmptyOK
Customer	Occupation	Class	Doctor,Software	FALSE

In my original data file, the bolded entries are currently returned as follows:

RecordID	Occupation	FirstName	LastName	Age	YearsOfEducation	YearsAtCurrentJob	Salary
1	Doctor	Ronald	Dryer	43	16	5	83000
2		Angelina	Prissant	33	18	4	87000
3	Doctor/Dentist	Tara	Jackson	28	12	6	35000
4	Doctor/Oncologist	Keith	Jacobs	56	14	8	63000
5	Doctor/Pediatrician	Terrence	McKinley	26	18	2	80000
6	Doctor/Dematologist	Wilma	Holmes	63	16	12	77000
7	Doctor	Mark	Hodges	46	12	2	34500
8	Doctor/Neurologist	Danielle	Rodriguez	29	22	1	108000
9	Software	Michael	Lawrence	53	16	7	93000
10	Software	Michael	Lawrence	53	16	7	930
11	Software	Christina	Kim	32	16	4	88000
12	Unemployed	Christina	Li	32	16	4	88000

This is working as expected. However, if the requirement classes are inputted as "Doctor*,Software" with a wildcard, I want all occupations starting with "Doctor" to be considered valid, resulting in the following output:

FileName	RecordID	Occupation	FirstName	LastName	Age	YearsOfEducation	YearsAtCurrentJob	Salary	InvalidField
Customer	2	[Null]	Angelina	Prissant	33	18	4	87000	Occupation
Customer	12	Unemployed	Christina	Li	32	16	4	88000	Occupation

How can I modify my workflow to support this wildcard option? I want to ensure that the existing functionality without wildcards remains unchanged.

I've attached my current workflow for reference.

[shancmiralles]

hi! can't we do a text to row for the occupation column of your input data like what you did with the R input? tysm 

[hcho]

Can you expand more??

[shancmiralles]

You use a text to column tool on the workflow you uploaded for your R Input ..
can't you do the same thing for your data (also split to rows)? 
then join them with the occupation column and class column? 
thanks!

[hcho]

I'm trying to use a wildcard because the delimiter may not always be "/". If "doctor" appears in both columns, I'd like to capture those records in the third join (J) anchor.

Can regex be used in the Join tool to achieve this?

[shancmiralles · Accepted solution]

hi @hcho 

you have to make your wildcard not a wildcard..... 

try using this formula:
REGEX_Replace([Classes], "[^a-zA-Z0-9 .-]", '/')

so, what this does.. your wildcard delimiter which is not a letter or a number  - is changed to "/". then you can parse it in a normal way  (text to column by row).. 
one of my mentors @Billd34  gave me the idea ... (which he got from  https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Regex-Special-Characters/td-p/...  ) 

i do hope this helps!
enjoy working on your WF! 😊