12 Replies Latest reply: May 2, 2010 4:49 AM by tauqueer RSS

    Join tables

      Hello,

      I'm new to qlikview and have a question about joining two tables.

      This is what I currently have :

      SalesInvHeader:

      LOAD

      autonumberhash256('UK',"No_") as Key_Inv,

      autonumberhash256('UK',"Sell-to Customer No_") as Key_Cust,

      'UK' AS NavCo,

      "No_" as Doc_Number,

      "Posting Date" AS Date,

      "Sell-to Customer No_",

      "Sell-to Customer Name",

      if ("Currency Factor"=0.00,1,"Currency Factor") as "Currency Factor"

      ;

      SQL SELECT *

      FROM "SERVER".dbo."Company$Sales Invoice Header"

      WHERE "Posting Date" >= '2009-01-03 00:00:00';

       

      SalesInvLine:

      LOAD

      autonumberhash256('UK',"Document No_") as Key_Inv,

      'UK' AS NavCoLine,

      "Document No_" as Doc_Number_Line,

      "Amount"

      WHERE EXISTS (Doc_Number,"Document No_");

      SQL SELECT [Document No_], SUM(Amount) AS Amount

      FROM [Company$Sales Invoice Line]

      GROUP BY [Document No_];

       

       

       

      error loading image

      I then need to calculate Amount (LCY)=(Amount/Currency Factor) but am unsure how to do it when the values are on separate tables, ideally I would just like to end up with one table.

        • Join tables

          hi,

           

          you can use left join as follows

           

           

          SalesInvHeader:

          LOAD

          autonumberhash256('UK',"No_") as Key_Inv,

          autonumberhash256('UK',"Sell-to Customer No_") as Key_Cust,

          'UK' AS NavCo,

          "No_" as Doc_Number,

          "Posting Date" AS Date,

          "Sell-to Customer No_",

          "Sell-to Customer Name",

          if ("Currency Factor"=0.00,1,"Currency Factor") as "Currency Factor"

          ;

          SQL SELECT *

          FROM "SERVER".dbo."Company$Sales Invoice Header"

          WHERE "Posting Date" >= '2009-01-03 00:00:00';

           

          SalesInvLine:

          left join (SalesInvHeader) LOAD

           

          autonumberhash256('UK',"Document No_") as Key_Inv,

          'UK' AS NavCoLine,

          "Document No_" as Doc_Number_Line,

          "Amount"

          WHERE EXISTS (Doc_Number,"Document No_");

          SQL SELECT [Document No_], SUM(Amount) AS Amount

          FROM [Company$Sales Invoice Line]

          GROUP BY [Document No_];

           

          thanks



          • Join tables
            Anatoly Pyatygo

            Hello David.

            You should join 2 tables by field Key_Inv:

            INNER JOIN(SalesInvLine)

            LOAD

            Key_Inv,

            [Currency Factor] AS [Currency Factor Line]

            RESIDENT SalesInvHeader;

             

            than you can calculate Amount(LCY) in each Line.

            • Join tables

               

              hi,

               

              you can use left join as follows

               

               

              SalesInvHeader:

              LOAD

              autonumberhash256('UK',"No_") as Key_Inv,

              autonumberhash256('UK',"Sell-to Customer No_") as Key_Cust,

              'UK' AS NavCo,

              "No_" as Doc_Number,

              "Posting Date" AS Date,

              "Sell-to Customer No_",

              "Sell-to Customer Name",

              if ("Currency Factor"=0.00,1,"Currency Factor") as "Currency Factor"

              ;

              SQL SELECT *

              FROM "SERVER".dbo."Company$Sales Invoice Header"

              WHERE "Posting Date" >= '2009-01-03 00:00:00';

               

              left join (SalesInvHeader) LOAD

               

              autonumberhash256('UK',"Document No_") as Key_Inv,

              'UK' AS NavCoLine,

              "Document No_" as Doc_Number_Line,

              "Amount"

              WHERE EXISTS (Doc_Number,"Document No_");

              SQL SELECT [Document No_], SUM(Amount) AS Amount

              FROM [Company$Sales Invoice Line]

              GROUP BY [Document No_];

               

              load (Amount/Currency Factor) as amount resident SalesInvHeader;

               

              thanks



                • Join tables

                   


                  tauqueer wrote:

                  load (Amount/Currency Factor) as amount resident SalesInvHeader;


                  <div></div>
                  Thanks for your quick reply.

                   

                  That has now now added the amount to the sales invoice header as i wanted, just a problem with the

                  load (Amount/Currency Factor) as amount resident SalesInvHeader; has now created another table called SalesInvHeader-1 that joins to the salesInvHeader on amount

                  Thanks

                    • Join tables

                      hi david,

                      you can use

                       

                      left join (SalesInvHeader) load (Amount/Currency Factor) as amount resident SalesInvHeader;

                       

                      it should work now..

                       

                      thanks

                        • Join tables

                          Hi,

                          Sorry again,

                          I have changed it to AmountLCY as i want it showing as a new field.

                          left join (SalesInvHeader) load (Amount/"Currency Factor") as AmountLCY resident SalesInvHeader;

                          But now for some reason it's duplicating the results and not working out the AmountLCY correctly

                          Thanks

                           

                            • Join tables

                              Hi david,

                              Pls post your application

                               

                              • Join tables

                                 

                                hi,

                                probably this should work

                                 

                                 

                                SalesInvHeader:

                                 

                                LOAD

                                autonumberhash256('UK',"No_") as Key_Inv,

                                autonumberhash256('UK',"Sell-to Customer No_") as Key_Cust,

                                'UK' AS NavCo,

                                "No_" as Doc_Number,

                                "Posting Date" AS Date,

                                "Sell-to Customer No_",

                                "Sell-to Customer Name",

                                if ("Currency Factor"=0.00,1,"Currency Factor") as "Currency Factor"

                                ;

                                SQL SELECT *

                                FROM "SERVER".dbo."Company$Sales Invoice Header"

                                WHERE "Posting Date" >= '2009-01-03 00:00:00';

                                 

                                left join (SalesInvHeader) LOAD

                                 

                                autonumberhash256('UK',"Document No_") as Key_Inv,

                                'UK' AS NavCoLine,

                                "Document No_" as Doc_Number_Line,

                                "Amount"

                                WHERE EXISTS (Doc_Number,"Document No_");

                                SQL SELECT [Document No_], SUM(Amount) AS Amount

                                FROM [Company$Sales Invoice Line]

                                GROUP BY [Document No_];

                                 

                                left join(SalesInvHeader) load *, (Amount/Currency Factor) as amount resident SalesInvHeader;

                                 

                                thanks



                        • Join tables
                          Stephen Redmond

                          Hi David,

                          You could try something like this:

                          SalesInvHeader:

                          LOAD

                          autonumberhash256('UK',"No_") as Key_Inv,

                          autonumberhash256('UK',"Sell-to Customer No_") as Key_Cust,

                          'UK' AS NavCo,

                          "No_" as Doc_Number,

                          "Posting Date" AS Date,

                          "Sell-to Customer No_",

                          "Sell-to Customer Name",

                          if ("Currency Factor"=0.00,1,"Currency Factor") as "Currency Factor"

                          ;

                          SQL SELECT *

                          FROM "SERVER".dbo."Company$Sales Invoice Header"

                          WHERE "Posting Date" >= '2009-01-03 00:00:00';

                           

                           

                          CurrMap:

                          Mapping Load autonumberhash256('UK',"No_"),"Currency Factor";

                           

                           

                          SQL SELECT Distinct "No_","Currency Factor"



                           

                          FROM "SERVER".dbo."Company$Sales Invoice Header"



                          WHERE "Posting Date" >= '2009-01-03 00:00:00' And "Currency Factor" <> 0;





                           

                           



                           

                           

                           

                           





                           

                           



                           

                          SalesInvLine:

                          LOAD

                          autonumberhash256('UK',"Document No_") as Key_Inv,

                          'UK' AS NavCoLine,

                          "Document No_" as Doc_Number_Line,

                          "Amount", ApplyMap('CurrMap',autonumberhash256('UK',"Document No_"),1) * "Amount" as LCY_Amount

                          WHERE EXISTS (Doc_Number,"Document No_");

                          SQL SELECT [Document No_], SUM(Amount) AS Amount

                          FROM [Company$Sales Invoice Line]

                          GROUP BY [Document No_];

                          Means that you can maintain the 2 tables separately which may have a performance benefit for you.

                          Regards,

                           

                          Stephen