8 Replies Latest reply: Oct 22, 2012 7:03 AM by Lav Jain RSS

    Joining tables

    Anuradha Abeysuriya

      I have loaded data using below tables

       

      ABC:

      LOAD

                XXX,

                AAA_NUM,

                STATUS_CODE,

          AAA_DATE,

                AAA_SET_ID;

       

      SQL SELECT

             XXX,

             AAA_NUM,

             STATUS_CODE,

                  AAA_DATE,

                   AAA_SET_ID

                   FROM table1 WHERE  STATUS_CODE <>'5' and XXX = ($(XXX))    

                AND TO_CHAR(AAA_DATE, 'YYYY') IN ($(ActiveYears));

                  

                  

                  

                  

      TRIAL_BALANCE_HISTORY:

      LOAD

           OUR_PROPORTION,

           AAA_SET_ID,

           TRANSACTION_DATE,

           PAYMENT_METHOD;

       

       

      SQL SELECT OUR_PROPORTION,

                   AAA_SET_ID,

                   TRANSACTION_DATE,

                   PAYMENT_METHOD FROM table2 WHERE

                 TO_CHAR(TRANSACTION_DATE, 'YYYY') IN ($(ActiveYears));

       

       

      Then i need to get sum of our_proportion. when i use sum it gives sum for all. but i need to filler it and get only sums accoring to where clause in 1st table.

       

      but those feils are not in the 2nd table.

       

      how can i solve this problem

       

      Please help

       

      Thanks

        • Re: Joining tables
          Lav Jain

          Hi Anuradhaa,

           

           

          use left join(ABC)

           

           

           

           

          Regards

            • Re: Joining tables
              Anuradha Abeysuriya

              Could you please help me to do that join. i'm don't know how to join tables in Qlikview

               

               

              Thanks

                • Re: Joining tables
                  Lav Jain

                  Hi,

                   

                  ABC:

                  LOAD

                            XXX,

                            AAA_NUM,

                            STATUS_CODE,

                      AAA_DATE,

                            AAA_SET_ID;

                   

                  SQL SELECT

                         XXX,

                         AAA_NUM,

                         STATUS_CODE,

                              AAA_DATE,

                               AAA_SET_ID

                               FROM table1 WHERE  STATUS_CODE <>'5' and XXX = ($(XXX))    

                            AND TO_CHAR(AAA_DATE, 'YYYY') IN ($(ActiveYears));

                   

                   

                  left join(ABC)

                   

                  LOAD

                       OUR_PROPORTION,

                       AAA_SET_ID,

                       TRANSACTION_DATE,

                       PAYMENT_METHOD;

                   

                   

                  SQL SELECT OUR_PROPORTION,

                               AAA_SET_ID,

                               TRANSACTION_DATE,

                               PAYMENT_METHOD FROM table2 WHERE

                             TO_CHAR(TRANSACTION_DATE, 'YYYY') IN ($(ActiveYears));

                   

                   

                  After the join the final table wud be named ABC, u can use some meaningful name instead of this

                   

                   

                   

                  Except this,i wud suggest that u create 2 qvds first having all the fields from both the tables w/o the where clause as it wud prevent hitting ur transaction table everytime you reload & also if u want to add some more fields from the same tables you can simply have them from the qvd.

                   

                  After creating the qvd take only the fields required here with the where clause from the qvd to perform the join.

                   

                   

                  Hope it helps !!!

                   

                  Regards