Look for non-match records

Anonymous — Wed, 10 Aug 2016 23:33:45 GMT

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 Number	ID	Date N	Date of I	Surname
3356	7289	08/06/2016	02/02/2016	A
5841	1189	05/09/2013	11/02/2011	B
1560	4328	28/03/2012	11/05/2010	C
5731	5200	16/02/2011	16/02/2011	D
9040	1417	30/09/2015	19/03/2011	F

Table 2:

Claim Number	Incident Number	ID	Date N	Date of I	Surname
2209E	Concurrent	5200	08/06/2016	02/02/2016	D
2862E	1753E	1417	04/09/2013	11/02/2011	E
9993E	3356	7289	28/03/2012	11/05/2010	A
84326	5841	189	16/02/2011	16/02/2011	B
78022	1560	4328	30/09/2015	30/09/2015	C

Re: Look for non-match records

Gysbert_Wassenaar — Thu, 11 Aug 2016 09:00:19 GMT

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.

topic Look for non-match records in Visualization and Usability

Look for non-match records

Re: Look for non-match records