Find an near match in a column to the qualifier in a column as requested below

[KarthikKumarV]

Hi All,

I'm new to Alteryx and got struck with a match function, kindly help me with a best solution.

I have below set of data and a column qualifier which needs to be matched and find the whole word of the qualifier.

DATA	QUALIFIER
ABC1B DEF11 GHI11 JKL11	ABC
DEF12 ABC12 JKL12	JKL
ABC12 DEF12 JKL14 XYZ2B MNO0B	DEF

I need the below solution in the end

DATA	QUALIFIER	Solution
ABC1B DEF11 GHI11 JKL11	ABC	ABC1B
DEF12 ABC12 JKL12	JKL	JKL12
ABC12 DEF1C JKL14 XYZ2B MNO0B	DEF	DEF1C

Thanks in advance 

[JosephSerpis · Accepted solution]

Hi @KarthikKumarV  I have mocked an example workflow that produces the output you described. Essentially I create a row per space in your data column then use a left function to get the first three characters then I match this to your qualifier and join back to your original data..

[KarthikKumarV]

Hey Thanks a lot, I too tried the same way earlier.

I'm looking for match using fuzzy match as my rows are more than 10K and there are other columns too which need to be remained same with the Data and qualifier. 

It would be great if i could extract the Data qualifier column from the data (everything before the space & its always 5 digit length) without creating record ID.

can you help me ?

Thanks

[MichalM]

@KarthikKumarV 

Are the strings you're trying to extract using the qualifier always a combination of letters and numbers only? Also, is it safe to assume it's always just one match?

[KarthikKumarV]

Yes its always one match and my qualifier will be followed by two digit (alpha numeric) always.

for Eg. ABC will have a match ABC12 or ABCAB or ABCA1

Consider ABC Is a family code and it child will be of 5 digit alpha numeric - (family code (ABC) + two digit alpha numberic)

[MichalM · Accepted solution]

@KarthikKumarV 

I quite like the solution provided by @JosephSerpis. If tweaked slightly, it will do exactly what you need and it's going to perform quite well even with larger quantities of data.

An alternative would be to build a batch macro with a Regex tool in Tokenize configuration and feed one record at a time. I've built an example which is attached

• You will feed in two parameters - your data string and the qualifier
• The data string will be used to filter your data to make sure only the relevant row is processed
• The qualifier will be used to dynamically build out a regular expression to extract just the string defined by the relevant qualifier

eg (ABC\w{2}) - in this case it will looked for a group starting with ABC followed by exactly 2 alphanumeric characters

[KarthikKumarV]

thanks all, both helped me.