Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
arunraj2308
New 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

Re: How many columns match between two tables

Hey Arun,

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

Best,

Sunny

arunraj2308
New Contributor

Re: How many columns match between two tables

Hi Sunny,

I was hoping to do it in script.

Thanks

Arun

Re: How many columns match between two tables

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
New Contributor

Re: How many columns match between two tables

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?

Community Browser