Dynamic Matching - How to go about it?

[caltang]

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!

[caltang]

@gawa this was the problem I briefly talked about the other day. I've finally found the time to post on the community. Hoping to learn/see how you would tackle it.

cc @Qiu as well to brainstorm! 😁

[Qiu · Accepted solution]

@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.

[caltang]

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! 

[gawa · Accepted solution]

@caltang

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😅 

[caltang]

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!!

[SPetrie · Accepted solution]

Two other possible options. First I could think of is using the regex as the boolean expression in a dynamic find/replace.

The other option is a variation of the macro concept, but using an iterative macro instead.

[caltang]

Thank you @SPetrie ! I will experiment further. The iterative one is pretty cool!

[caltang]

Hi @Qiu @gawa @SPetrie 

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!

[Qiu]

@gawa 
Good insight to learn.