3 Replies Latest reply: Nov 20, 2013 12:50 PM by Srikanth P RSS

    Missing rows between 2 QVD's

    Bill Markham

      Hi

       

      I have 2 QVD's, one from a Full data load QVD-FULL and the other which has been incrementally loaded QVD-INC.  In theory they should be identical, but unfortunately they have different numbers of rows.

       

      They both have a unique ID field called say ID.

       

      What would be an approach to find out :

      • ID values in FULL and not in INC
      • ID values in INC and not in FULL

       

      Here are a couple of small sample Inline Loads of test data.  In reality the QVD's have a few millions rows, but I could easily trim them down to a few hundred thousand rows and the comparison would still be meaningful.

       

      FULL:

      LOAD * INLINE [

          ID

          1

          2

          3

          8

          9

      ];

       

      INC:

      LOAD * INLINE [

          ID

          1

          4

          5

          6

          7

          9

      ];

       

       

      Best Regards,     Bill

        • Re: Missing rows between 2 QVD's
          Srikanth P

          You can Try with Exists function:

           

          FUL:

          LOAD ID FROM FULL.QVD;

           

          NOT_IN_FUL:

          LOAD ID FROM INC.QVD WHERE NOT EXISTS (ID);

           

          Another Method: Join the 2 tables with Flag:

           

          TEMP:

          LOAD ID, 'F' AS F_FLAG FROM FULL.QVD;

          JOIN

          LOAD ID,'I' AS I_FLAG FROM INC.QVD;

           

          NOT_IN_FULL:

          LOAD ID

          RESIDENT TEMP WHERE F_FLAG = NULL();

            • Re: Re: Missing rows between 2 QVD's
              Bill Markham

              Dathu

               

              I prefer your 2nd method, I did a couple of tweaks, ended up with this and it tests fine.

               

              TEMP:

              LOAD * INLINE [

                  ID , F_FLAG

                  1 , 'F'

                  2 , 'F'

                  3 , 'F'

                  8 , 'F'

                  9 , 'F'

              ];

               

              outer JOIN

              LOAD * INLINE [

                  ID , I_FLAG

                  1 , 'I'

                  4 , 'I'

                  5 , 'I'

                  6 , 'I'

                  7 , 'I'

                  9 , 'I'

              ];

               

              qualify * ;

              NOT_IN_FULL:

              LOAD *

              RESIDENT TEMP WHERE not F_FLAG = 'F' ;

               

              I just need now to run it against few large production QVD's.

               

              Many Thanks,     Bill