5 Replies Latest reply: Dec 8, 2016 8:38 AM by Michael Goldshteyn RSS

    Comparing two tables

    Michael Goldshteyn

      Greetings Community,

       

      I am attempting to create a temp table to display ID's that are in one table but not the other.

       

      For example.

       

       

      [People Attributes]:

      LOAD

         ID as "Learner ID",
      .........

      FROM [SomeFile.xlsx]

      (ooxml, embedded labels, table is page);

       

      [Distribution List]:

      LOAD

         ID as "Learner ID",
      .........

      FROM [SomeFile2.xlsx]

      (ooxml, embedded labels, table is page);

       

       

      is there a way to make a temp table through QlikSense that will store all the IDs that are in the table [Distribution List] and not in the table [People Attributes].

        • Re: Comparing two tables
          Allu Allu

          try with where not  exists() ...

          • Re: Comparing two tables
            Anand Chouhan

            Try this way by creating the

             

            [People Attributes]:

            LOAD

               ID as "Learner ID",
            .........

            FROM [SomeFile.xlsx]

            (ooxml, embedded labels, table is page);

             

            [Distribution List]:

            LOAD

               ID as "Learner ID",
            .........

            FROM [SomeFile2.xlsx]

            (ooxml, embedded labels, table is page);

             

            Store [Distribution List] into [Distribution List].qvd(qvd);

             

            Regards

            Anand

            • Re: Comparing two tables
              Allu Allu

              [People Attributes]:

              LOAD

                ID as "Learner ID",

              .........

              FROM [SomeFile.xlsx]

              (ooxml, embedded labels, table is page);

               

               

               

               

               

              [Distribution List]:

              LOAD

                ID as "Learner ID",

              .........

              FROM [SomeFile2.xlsx]

              (ooxml, embedded labels, table is page)

              WHERE EXISTS(ID);

              • Re: Comparing two tables
                Anand Chouhan

                If you want to store the data then try this way

                 

                [People Attributes]:

                LOAD

                   ID as "Learner ID",

                   'People Attributes' as TabFlag
                .........

                FROM [SomeFile.xlsx]

                (ooxml, embedded labels, table is page);

                 

                [Distribution List]:

                LOAD

                   ID as "Learner ID",

                   'Distribution List' as TabFlag
                .........

                FROM [SomeFile2.xlsx]

                (ooxml, embedded labels, table is page);

                 

                NoConcatenate

                [Only Distribution List]:

                LOAD * Resident [People Attributes] Where TabFlag = 'Distribution List';

                 

                STORE [Only Distribution List] into [Only Distribution List].qvd(qvd);   //Later you can use this qvd in your data model

                DROP Table [Only Distribution List];

                 

                 

                Regards

                Anand

                • Re: Comparing two tables
                  Michael Goldshteyn

                  I was actually able to achieve my needed results from doing a left join on the two tables and calculate the needed data from that.