Dynamic Matching - How to go about it?

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!

Parse

Best Practices

Batch Macro

Preparation

Accepted answers

Qiu

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

0421-caltang.yxzp

Gawa

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

1264039_gawa.yxmd

SPetrie

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.

iterate output.PNG

MapCheck.yxzp

All comments

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

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

0421-caltang.yxzp

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

@caltang

It seems @Qiu already solved but I still have some clarification about your case.

1264039_gawa.yxmd

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

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.

iterate output.PNG

MapCheck.yxzp

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703