Match on multiple possible combinations of fields

[cowannbell]

I was wondering if there was an easy way to take two files or two data sources and find a match based on the possible match of three different fields.

I. E.  I have source one that has three fields. PIN, Address# and TIN.  One, two or all three might have data in the fields.  There can be any combination of the three.

I have source/file 2 that has PIN, Address# and TIN.  I want to try to the match the two files on any combination of the fields.  I would want to start the match on all three fields and then any two fields and then any one field.

I know how to do the matching but I would have to do the matching multiple times in my way.  I was hoping there maybe a better way?

Thanks,

[mceleavey]

Hi @cowannbell ,

Can you please provide some data (mock data is fine providing it is representative of what you are trying to achieve) so we can help you with the problem?

Thanks,

M.

[Thableaus]

Hi @cowannbell 

You could try to build an iterative macro for that, if you want to "templatize" it.

But in my opinion, you would still have to use multiple join tools and use the left and right side of the join as your input in the subsequent joins.

Another way would be setting up formulas to flag matches based in IF conditions, and then filter those flags.

You could use an AppendFields tools to basically create every possible record combination from these two sources

Then, use the Formula Tool to compare the field logic

Like If FieldA = Field B = Field C THEN "T"

ELSEIF FieldA = FieldB THEN "T"

and so on...

Cheers,

[cowannbell]

Thanks.  I think the formula field might be an easier option.  I'll try it out and see if it works.