Compare 2 field and find the difference from 2 table

[heather97]

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);

QlikView 

[oskartoivonen]

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);

[heather97]

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

[oskartoivonen]

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

[heather97 · Accepted solution]

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? 

[oskartoivonen]

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.