Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
arunraj2308
Contributor
Contributor

How many columns match between two tables

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

4 Replies
sunny_talwar

Hey Arun,

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

Best,

Sunny

arunraj2308
Contributor
Contributor
Author

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:


Capture.PNG


arunraj2308
Contributor
Contributor
Author

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?