topic Re: Compare 2 field and find the difference from 2 table in QlikView

Compare 2 field and find the difference from 2 table

heather97 — Mon, 05 Sep 2022 05:02:54 GMT

Hi everyone, looking for some help!

Suppose I have 2 tables with the same exact fields. I want to find the new Name and country from table 2 as compared to table1. If the Name and country does not match with the Name and country in table 1 then it will be created in a new table.

E.g.

Table1:

Name	Country	Gender
John	USA	Male
Kevin	UK	Male
Mary	UK	Female
June	UK	Female

Table 2:

Name	Country	Gender
Ariel	USA	Female
Kevin	UK	Male
Mary	UK	Female
John	UK	Male

Result will be:

Name	Country	Gender
Ariel	USA	Female
John	UK	Male

Since John in Table 2, his country is different from table 1, thus, John - UK should be added in the new table.

Directory;
Table1:

LOAD Name as uniqueName,
Country,
Gender
FROM
[File1.xlsx]
(ooxml, embedded labels, table is Table1);

Directory;
Table2:
Concatenate(Table1)
LOAD Name,
Country,
Gender
FROM
[File2.xlsx]
(ooxml, embedded labels, table is [Table2])
WHERE Not Exists(UniqueName, Name);

Re: Compare 2 field and find the difference from 2 table

oskartoivonen — Mon, 05 Sep 2022 06:09:51 GMT

If you want to check for multiple fields, they need to be included in your exists filter.

Maybe this does what you need?

Table1: LOAD Name, Country, Gender, Name &'|'& Country &'|'& Gender as PersonFilter FROM [File1.xlsx] (ooxml, embedded labels, table is Table1); CONCATENATE(Table1) Table2: LOAD Name, Country, Gender FROM [File2.xlsx] (ooxml, embedded labels, table is Table2) WHERE not Exists(PersonFilter, Name &'|'& Country &'|'& Gender);

Re: Compare 2 field and find the difference from 2 table

heather97 — Mon, 05 Sep 2022 06:14:08 GMT

Hello, thanks for the reply, would try that out. Do I need to include the gender if I am ignoring that field for filter?

Re: Compare 2 field and find the difference from 2 table

oskartoivonen — Mon, 05 Sep 2022 06:15:43 GMT

Only include the fields which you need to combine and compare, so if name and country are sufficient, use just those two.

Re: Compare 2 field and find the difference from 2 table

heather97 — Mon, 05 Sep 2022 06:21:00 GMT

It works. Thank you so much! Can I check with you, so if there's 2 same row in table 2, it will only be included one time am I right?

Re: Compare 2 field and find the difference from 2 table

oskartoivonen — Mon, 05 Sep 2022 06:23:31 GMT

If you want to avoid duplicates from Table2, you need to create the PersonFilter-field in Table2 LOAD as well. If you do this, it'll add the new row to the filter after concatenation and prevent additional rows from the same LOAD.