1 Reply Latest reply: Aug 11, 2016 5:01 AM by Gysbert Wassenaar RSS

    Look for non-match records

    jade wind

      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
        • Re: Look for non-match records
          Gysbert Wassenaar

          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.