Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.