Find join between tables where at least x number of fields match

[akasubi]

I have 2 tables with 8 columns each. The column names for each table are the same. I want to find a join between these 2 tables where at least 6 or 7 of the columns match

e.g. I have columns A-H and want to see where there is a join between columns A-G or B-H or any combination of the 7 columns, if this makes sense.

Is this possible and if so, how can it be done?

Thanks!

[afv2688]

Hello @akasubi ,

Check this workflow:

It compares and iterates between the columns, always comparing 7 of the 8 columns at the same time, afterwards it tells you wich columns has been skipped and if it finds a match it will tell you between which records from the input it has been done.

Hope this helps.

Regards

[bensilv]

EDIT: the ACE reply above may be a more elegant way forward!

It would be useful to understand what the goal of this join is?

If you have two tables and want to check whether they have 6 or 7 columns that match, it sounds like a reconciliation process to me? Have you considered the following idea:
Transposing the data before adding a new column to reconcile the corresponding rows, creating a series of checks

E.g. Flag 1 checks if column 1 from dataset A equals column 2 from dataset B, same for Flag 2 and so on for all 8 fields? Then sum the flags you have created to see if you hit that 6 or 7 threshold.

I hope that makes sense, but to summarize: I think you need to perform reconciliations and create flags.

[AngelosPachis]

Hi @akasubi ,

I would suggest you transpose your two tables to bring them in a format where you have the column headers in one field and the values in another. Then after converting the values to a string, you can check if the values are identical between the two tables for each record.

Then, if you want an exact number for matched columns, you can add a summarize tool to count the number of matched columns per record.

The example I've used above is quite symbol, but it should work for more complex tables as well.

Hope that helps, let me know if that works for you.

Regards,

Angelos

[akasubi]

Hi @bensilv, it's sort of like a fuzzy match if I were to concatenate the values of the columns together into a single column. So I'm looking to see if I can find a match where say 90% of the column value matches, if that makes sense.