2 Replies Latest reply: Aug 7, 2012 2:46 AM by Marcin Sworzynski RSS

    join and aggregate

    Marcin Sworzynski

      Hi Dear Qlikview Experts

      I have 2 tables

      TABLE A:

      customer_id

      item_id

      amount

      TABLE B:

      item_id

      dimension1

       

      Question is: is it possible to join and aggregate data to dimension1 level in QlikView ? At the end I would like to have in QlikView results of SQL query:

      Select customer_id,dimension1,sum(amount) from TABLE_A inner join TABLE_B on TABLE_A.item_id=TABLE_B.item_d. I know I can do it using SQL statement in script, but is it possible to do it QlikView engine ?

        • Re: join and aggregate
          Mayil Vahanan Ramasamy

          Hi

           

             Are you like this?

           

          A:

          Load * Inline

          [

          Customer_id,Item_id,amount

          1,1,100

          2,2,200

          4,3,300

          4,3,400

          ];

          B:

          Load * Inline

          [

          Item_id,dimension1

          1,XXX

          3,YYY

          4,ZZZ

          ];

           

           

          C:

          NoConcatenate

          LOAD Customer_id,Item_id,Sum(amount) as Amount Resident A Group by Customer_id,Item_id;

          Inner join(C)

          LOAD Item_id,dimension1 Resident B;

           

           

          DROP Tables A,B;

            • Re: join and aggregate
              Marcin Sworzynski

              Thanks for your answer. I'm afraid that result of your script won't be the same as result of following sql query:

               

              customer_id,dimension1,sum(amount) from TABLE_A inner join TABLE_B on TABLE_A.item_id=TABLE_B.item_d

              group by customer_id,dimension1

               

              I need to group data in TABLE A by using dimension from TABLE B