Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
heather97
Partner - Contributor II
Partner - Contributor II

Compare 2 field and find the difference from 2 table

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 

Labels (2)
1 Solution

Accepted Solutions
heather97
Partner - Contributor II
Partner - Contributor II
Author

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? 

View solution in original post

5 Replies
oskartoivonen
Partner - Contributor III
Partner - Contributor III

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
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Contributor III
Partner - Contributor III

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

heather97
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Contributor III
Partner - Contributor III

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.