Find an near match in a column to the qualifier in a column as requested below
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Fuzzy Match
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator