Multi Column Match

[rakeshseeram]

Hello Everyone.

I need help in understanding on how to see if there are any common elements between 2 data sets. Lets say there are 2 files. File 1 has the columns A,B & C and File 2 has the columns X,Y & Z. i want to see if the data in column A matches with any column in File 2 and get that matched column name. Like wise i want to check all the columns in File 1. I want your help to understand how to check that. 

The actual database where i need to verify has 177 columns in File 1 and 43 columns in File 2. it will be very laborious to manually configure each field and check the match. Looking forward to see if there is any simpler way to deal with this. Thanks in advance. 

[CatheyH]

Not sure if this is the most efficient method - it will very much depend on the number of rows, data types etc:

Essentially turning each column into a concatenated text field, joining on it and seeing which values match. 

You could always limit to (say) 5 rows to eliminate the majority of columns quite quickly.

Hope it helps!

[SeanAdams]

Hey @rakeshseeram 

The place to start here is how to link a given row across the two data sets - otherwise you're trying to match a single cell in a 177 column wide multi-row sheet with a single cell in a 34 column wide sheet.      So you need to have a key that will allow you to match a particular row in data set A to dataset B.

Once you have that - you can then transpose the data for each row into name-value pairs and find which combination of columns gives you the highest match rate.

Do you have a common key between the two data sets to work with?

[Qiu]

@rakeshseeram 
It is difficult for me to come up something good.
For a start, I use the Append tool to have all the combimations after Transpose, so it is going to be big data for your case.

And then I try to count the Match Case for all the combination and I assume it is a match if the sum of matches equals the record account.

But it wont work if 

1. The data in each column has duplications

2. The record number for each columns not same all the time

So maybe we need a batch macro after all?

[CatheyH]

Hi,

This doesn't solve the problem of the large append-all, but it does solve the problem of duplicate rows or different table sizes: