4 Replies Latest reply: Jul 13, 2016 1:27 PM by Kash Jani RSS

    Find difference between two tables

    Kash Jani

      Hello

       

      I have below (sample) two tables with each loading some date. I'm concacting both tables.

       

      While I concatanate, I would like to flag the values that are not matching through a new field. In below example, ExternalID A001 in TableA  is not found in other table. Could I use anything in Qlikview that will help me add a flag (say new value i.e. A001)?


      I could then use this flag to identify in qlikview which new values have been added.

       

      Many Thanks for your help.

       

      TableA:
      Load * INLINE
      [
      Country, SnapshotNo, ExternalID
      AM, 70, A001
      BE, 68, A002
      ]
      ;
      Concatenate
      Load * INLINE
      [
      Country, SnapshotNo, ExternalID
      AM, 69, A003
      BE, 68, A002
      ]
      ;

       

        • Re: Find difference between two tables
          rodrigo silva

          I think abaout this:

           

          TableA:
          Load * INLINE
          [
          Country, SnapshotNo, ExternalID
          AM, 70, A001
          BE, 68, A002
          ]
          ;


          Concatenate

          load *, 'not find' as flag where not exist (ExternalID);
          Load * INLINE
          [
          Country, SnapshotNo, ExternalID
          AM, 69, A003
          BE, 68, A002
          ]
          ;


          Concatenate

          load *,  where exist (ExternalID);
          Load * INLINE
          [
          Country, SnapshotNo, ExternalID
          AM, 69, A003
          BE, 68, A002
          ]
          ;



          • Re: Find difference between two tables
            Rakesh Boorgu

            TableA:
            Load * INLINE
            [
            Country, SnapshotNo, ExternalID
            AM, 70, A001
            BE, 68, A002
            ]
            ;
            Concatenate

            LOAD *

            where not exists(ExternalID)

            ;

            Load * INLINE
            [
            Country, SnapshotNo, ExternalID
            AM, 69, A003
            BE, 68, A002
            ]
            ;



            **Please be aware - the above approach works for distinct ExternalIDs - if they are not distinct, you will have create a temp table, with distinct ExternalIDs as field with a different name - and then use that in that in the where clause.

            • Re: Find difference between two tables
              Andrew Walker

              Hi Kash,

                                  Yet another suggestion:

               

              TableA:

              Load * INLINE

              [

              Country, SnapshotNo, ExternalID, Flag

              AM, 70, A001,0

              BE, 68, A002,0

              ];

               

               

              NoConcatenate

              TableB:

              LOAD

              Country, SnapshotNo, ExternalID as ID;

              Load * INLINE

              [

              Country, SnapshotNo, ExternalID

              AM, 69, A003

              BE, 68, A002

              ];

               

               

              Concatenate(TableA)

              LOAD

              Country, SnapshotNo, ID as ExternalID,

              If(not Exists(ExternalID,ID),1,0) as Flag

              Resident TableB;

              Drop table TableB;

               

              A flag value of 1 will indicate a new value of ExternalID.

               

              ExternalID Country SnapshotNo Flag
              A001AM700
              A002BE680
              A003AM691

               

              Cheers

               

              Andrew

              • Re: Find difference between two tables
                Kash Jani

                Thanks very much for your time and suggestions. Much appreciated.