How many columns match between two tables

[arunraj2308]

Hi,

I have two Tables both with the same column names. I need to find out how many columns match between the two tables -

Table 1:

Name, DOB, City, Country

Emily, 20/10/1986, London, UK

Peter, 10/06/1992, New York, US

Kevin, 23/08/1982, Sydney, Australia

Rose, 08/11/1981, Paris, France

Table 2:

Name, DOB, City, Country

Emily, 20/10/1987, London, UK

Peter, 10/06/1992, New York, US

Kevin, 23/10/1982, Auckland, NewZealand

Gary, 19/01/1983, Berlin, Germany

Resulting Table should take Table 1 as the main table :

Table 3:

Name, DOB, City, Country, MatchingFields

Emily, 20/10/1986, London, UK, Matching on Name and City and Country

Peter, 10/06/1992, New York, US, Matching on Name and DOB and City and Country

Kevin, 23/08/1982, Sydney, Australia, Matching on Name

Rose, 08/11/1981, Paris, France, No Matches

Any Help will be highly appreciated.

Thank You

Arun

[sunny_talwar]

Hey Arun,

Do you need this on the front end or in script also works?

Best,

Sunny

[arunraj2308]

Hi Sunny,

I was hoping to do it in script.

Thanks

Arun

[sunny_talwar]

Arun, this may not be the best way to do it, but see if this helps and can be further tweaked for your needs:

Table1:

LOAD * Inline [

Name, DOB, City, Country

Emily, 20/10/1986, London, UK

Peter, 10/06/1992, New York, US

Kevin, 23/08/1982, Sydney, Australia

Rose, 08/11/1981, Paris, France

];

Table2:

LOAD Name as NameCheck,

  DOB as DOBCheck,

  City as CityCheck,

  Country as CountryCheck;

LOAD * Inline [

Name, DOB, City, Country

Emily, 20/10/1987, London, UK

Peter, 10/06/1992, New York, US

Kevin, 23/10/1982, Auckland, NewZealand

Gary, 19/01/1983, Berlin, Germany

];

Table:

NoConcatenate

LOAD *

Resident Table1;

Join(Table)

LOAD *,

  1 as NameMatch

Resident Table1

Where Exists(NameCheck, Name);

Join(Table)

LOAD *,

  1 as DOBMatch

Resident Table1

Where Exists(DOBCheck, DOB);

Join(Table)

LOAD *,

  1 as CityMatch

Resident Table1

Where Exists(CityCheck, City);

Join(Table)

LOAD *,

  1 as CountryMatch

Resident Table1

Where Exists(CountryCheck, Country);

DROP Tables Table2, Table1;

FinalTable:

LOAD *,

  If(SubField(Trim(If(Len(Trim(NameMatch)) = 0 and Len(Trim(DOBMatch)) = 0 and Len(Trim(CityMatch)) = 0 and Len(Trim(CountryMatch)) = 0,

  'No Match', 'Match on ' &

  If(NameMatch = 1, 'Name and ') & If(DOBMatch = 1, 'DOB and ') &

  If(CityMatch = 1, 'City and ') & If(CountryMatch = 1, 'Country'))), ' ', -1) = 'and',

  Left(Trim(If(Len(Trim(NameMatch)) = 0 and Len(Trim(DOBMatch)) = 0 and Len(Trim(CityMatch)) = 0 and Len(Trim(CountryMatch)) = 0,

  'No Match', 'Match on ' &

  If(NameMatch = 1, 'Name and ') & If(DOBMatch = 1, 'DOB and ') &

  If(CityMatch = 1, 'City and ') & If(CountryMatch = 1, 'Country'))),

  Index(Trim(If(Len(Trim(NameMatch)) = 0 and Len(Trim(DOBMatch)) = 0 and Len(Trim(CityMatch)) = 0 and Len(Trim(CountryMatch)) = 0,

  'No Match', 'Match on ' &

  If(NameMatch = 1, 'Name and ') & If(DOBMatch = 1, 'DOB and ') &

  If(CityMatch = 1, 'City and ') & If(CountryMatch = 1, 'Country'))), ' ', -1)-1),

  If(Len(Trim(NameMatch)) = 0 and Len(Trim(DOBMatch)) = 0 and Len(Trim(CityMatch)) = 0 and Len(Trim(CountryMatch)) = 0,

  'No Match', 'Match on ' &

  If(NameMatch = 1, 'Name and ') & If(DOBMatch = 1, 'DOB and ') &

  If(CityMatch = 1, 'City and ') & If(CountryMatch = 1, 'Country')))

  as Match

Resident Table;

DROP Table Table;

Output in a table box:

[arunraj2308]

Hi Sunny,

Thanks a lot. I was trying to do this using a loop. But your method is much better and easier.

One last thing, I should have mentioned this earlier. So stupid of me.

I have got one more field that i need to match i.e. Address and I do not need a complete match for this field. It can be a complete or partial match. Even if it is a partial match it is match.

For example

Table1:

LOAD * Inline [

Name, DOB, City, Country, Address

Emily, 20/10/1986, London, UK, abcd

Peter, 10/06/1992, New York, US, 1234

Kevin, 23/08/1982, Sydney, Australia, wxyz

Rose, 08/11/1981, Paris, France, 5678

];

Table2:

LOAD Name as NameCheck,

  DOB as DOBCheck,

  City as CityCheck,

  Country as CountryCheck,

Address as AddressCheck;

LOAD * Inline [

Name, DOB, City, Country, Address

Emily, 20/10/1987, London, UK, ab78 

Peter, 10/06/1992, New York, US, 1234

Kevin, 23/10/1982, Auckland, NewZealand, 90yz

Gary, 19/01/1983, Berlin, Germany, jhg

];

So for

Emily - Match on Name and City and Country and Address

The exists () function seems to do an exact match.Is there a similar function which does partial matches?