Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jadewind
Contributor

Look for non-match records

Hi everyone,

I need to check data quality of a table against another table. Please see below two tables as an example. Table 1 would be the source table. And Table 2 would be the data entry of Table 1 with an extra unique key field.

I am hoping that I can load these two tables in QS and use QS to design a result table to show only discrepancies between these two tables.

How would you do it? I normally use combination keys in Excel to do it (eg. making a synthetic key using all of Table 1's columns to compare against Table 2's same synthetic key). But I thought maybe QS can do it faster if it CAN do it?

Any tips will be much appreciated!

Table 1:

Incident NumberIDDate NDate of ISurname
3356728908/06/201602/02/2016A
5841118905/09/201311/02/2011B
1560432828/03/201211/05/2010C
5731520016/02/201116/02/2011D
9040141730/09/201519/03/2011F

Table 2:

Claim NumberIncident NumberIDDate NDate of ISurname
2209EConcurrent520008/06/201602/02/2016D
2862E1753E141704/09/201311/02/2011E
9993E3356728928/03/201211/05/2010A
84326584118916/02/201116/02/2011B
780221560432830/09/201530/09/2015C
1 Reply

Re: Look for non-match records

Basically the same:

Table1:

LOAD * , autonumber( Field1 & '|' & Field2 & '|' & ..etc ) as CompareKey1

FROM ...table1source... ;

NOCONCATENATE

Table2:

LOAD * , autonumber( Field1 & '|' & Field2 & '|' & ..etc ) as CompareKey2

FROM ...table2source... ;

NOCONCATENATE

Table3:

LOAD *, 'Only in Table 1' as Comment RESIDENT Table1 WHERE NOT Exists(CompareKey2, CompareKey1);

CONCATENATE (Table3)

LOAD *, 'Only in Table 2' as Comment RESIDENT Table2 WHERE NOT Exists(CompareKey1, CompareKey2);

...and then do something to clean up because otherwise you're going to have a big synthetic key problem.