Dynamic Matching - How to go about it?
- 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 have the following data:
RecordID | RandomDescriptionName |
1 | The Jet Liner AAR Manshowitzs |
2 | Domicile Thunder LLC Capital |
3 | King of Prussia ZZDE Capital |
4 | Breslau 778 GA-STR Demand |
5 | Bavaria Man GL-SST |
I have the following mapping table:
RecordID | Map |
1 | LLC |
2 | GA-STR |
3 | AAR |
4 | ZZDE |
The above is NOT mapped by RecordID or position. It is based on the words of Map in the Description. The Description name as well can change, so I think a Dynamic Rename is needed to force to the name to standardize.
I need to use Regex_CountMatches([String],"Pattern") in order to map it, but I am looking for a dynamic solution - a batch macro if you will, to resolve it.
The idea is to have the first data set go row by row, and then compare it against the mapping list. Whichever is a hit, then it gets through. If it's not hitting any Map, then I want it to go out.
I'm facing some difficulty on this and I am not sure how to tackle it. I've got the basic idea down, and I'd love to hear from others or see how others would tackle it.
For context, I have about 800 possible Mapping rows. All help appreciated!
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
Solved! Go to Solution.
- Labels:
- Batch Macro
- Best Practices
- Parse
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@caltang
Maybe I have not fully understand the problem here, and I can use a Batch macro to do this as below.
As in case of multiple match, I use a Summarize tool for concataenating.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you @Qiu ! For some reason I kept putting the control parameter on the filter tool and kept trying to change the word “pattern” as the action in the expression Regex_CountMatches([String],”pattern”).
I realized your solution is going through the record ID and then spitting out the descriptions. I’ll study it further but I think this should solve it!
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It seems @Qiu already solved but I still have some clarification about your case.
It is simply doable by Cross-join 'Data table' and 'Mapping table', and filter by Contains() function. This method will examine records N x M times as it is a kind of brute force approach. Using Batch macro is basically same concept, because it examines M times in one batch, and that iterates N times; M x N times. Rather, Batch macro will take the extra overhead to call Macro inside process, that's not way I do as per my experience in Advent of Code😅
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hmm that is true and I appreciate your view @gawa !
But my use case has over 3k rows and 800 matches. That works out to 2.4million combinations alone… might be a stretch but do you recommend it?
I am looking to learn and see what works best. Thanks Gawa-san and Qiu-san!!
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you @SPetrie ! I will experiment further. The iterative one is pretty cool!
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
All three methods work great! In terms of speed, Gawa is right as Qiu's and SPetrie's macros can take quite a lot processing power and I noticed that it is slower than brute force on my PC. But in terms of solving it, all three did what I needed!
Thank you for helping me on a weekend, really appreciate it!
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@gawa
Good insight to learn.
