11 Replies Latest reply: Jun 27, 2012 7:24 AM by Stefan Wühl RSS

    Joining data field in calculation from two tables

      Hi,

       

      I am a beginner at using/developing in QV so excuse my rudimentary questions..:)

       

      1.) I would like to use a data field from one table (loaded into script) as a value in another table. The two tables are joined by primary key.

       

      Example:

      When trying to use the data field "OrderQty" from table "OrderDetail"  - in - table "Product" (which doesn´t contain a data field form QrderQty) I get an error message.. when loading data.

       

      How do I use the "join" function to be able to calculate new values in the table "Product" with the help of data field "OrderQty"???

       

      BR

      Kriss

        • Re: Joining data field in calculation from two tables
          Prasath Palani

          Hi,

           

          Instead of Joining, you can link these 2 tables with the primary key,

           

          The advantage of qlikview is AQL (Associative Query Logic), Which give more felxibility on data association.

           

          AQL is a query language based on a data base management system which uses Codd's relational model of data. It has been designed mainly to be used by the nonspecialist in data processing for interactive problem solving, application building, and simulation. Ease of use is achieved by providing an interface which allows the use of default options, synonyms, and definitions of attributes, inference, and the possibility of interactive completion of the query (i.e., menu). AQL combines the capabilities of the relational model of data with the powerful computational facilities and control structure of the host programming language (i.e., APL). A prototype version of AQL, which has been implemented, is reviewed.

           

          Regards,

          Prasath

            • Re: Joining data field in calculation from two tables

              Hi,

              thanks for very rapid reply!

               

              - How do I create(use) the value OrderQty in the table "Product"

               

              The calculation I would like to acheive is to use values for OrderQty together with StandardCost to create totCost... (OrderQty * StandardCost)... but I do not know how to load / join / connect the two data fields from the two separate tables 

              - and they are joined by a p key "ProductID"

               

              -- Please show how to make the calculation in one of the tables

               

              .......

              SalesOrder:

              LOAD SalesOrderID,

                   ProductID,

                   UnitPrice,

                  OrderQty,

                   OrderQty * UnitPrice as Sales1

                  

              FROM

              [..\\QVD\SalesOrder.qvd]

              (qvd);

               

               

              Product:

              LOAD ProductID,

                   Name,

                   ProductNumber,

                  StandardCost,

                   ListPrice

               

              FROM

              [..\\QVD\Product.qvd]

              (qvd);

               

               

              BR

              Kriss

                • Re: Joining data field in calculation from two tables
                  Prasath Palani

                  Hi,

                   

                  Yes they are joined using primary key ProductID.

                   

                  press CTRL+T, which will show the table structure how the tables are linked in the application.

                   

                  Step - 1.

                  Add Straight Table -> in dimensions put ProductID.

                   

                  Step - 2

                  Go to expressions -> sum(Orderqty) * sum(standardcost)

                  name this expression as totalcost

                   

                  Regards,

                  Prasath

                    • Re: Joining data field in calculation from two tables

                      Hi Prasath!

                       

                      Thank you for your attention and assistance.

                       

                      - so you mean that I cannot create this new value " totCost" in the code in the script editor ?

                      Do I have to use a straight table / object... I will want to use this new value "totCost" several times again and would like for it to be in the script

                      - should I perhaps create a new table (in the script) with the two values in it?

                       

                       

                      BR

                      Kriss

                        • Re: Joining data field in calculation from two tables
                          Stefan Wühl

                          You can join your tables or you could f.e. use a lookup:

                           

                           

                           

                           

                          Product:

                          LOAD ProductID,

                               Name,

                               ProductNumber,

                              StandardCost,

                               ListPrice

                           

                          FROM

                          [..\\QVD\Product.qvd]

                          (qvd);

                           

                          SalesOrder:

                          LOAD SalesOrderID,

                               ProductID,

                               UnitPrice,

                              OrderQty,

                               OrderQty * UnitPrice as Sales1,

                               OrderQty * lookup('StandardCost','ProductID',ProductID,'Product') as TotalCost

                              

                          FROM

                          [..\\QVD\SalesOrder.qvd]

                          (qvd);

                            • Re: Joining data field in calculation from two tables

                              Hi

                               

                              thank you very much for assisting.

                              The code doesn´t seem to work properly..

                               

                              - there is no calculation done for the value.. "TotalCost"

                               

                              Can there be some error in syntax..

                               

                              OrderQty * lookup('StandardCost','ProductID',ProductID,'Product') as TotalCost

                               

                              the table name is "Product" .. and the primary key for both tables is "ProductID"...

                               

                               

                               

                              BR

                              Kriss

                                • Re: Joining data field in calculation from two tables
                                  Stefan Wühl

                                  There can always be an error, I haven't tested the code. Please note that the Product table needs to be loaded already (that's why I changed the order of loads), when using the lookup ...

                                    • Re: Joining data field in calculation from two tables

                                      Hi Swuehl,

                                       

                                      it seems to be working after a couple of reloads... (?) !!

                                      Thank you very much for your assistance.

                                       

                                      Which is the correct syntax formula for lookup - lookup('fieldname' , 'pkey_?' , 'pkey ?' , )   ?

                                       

                                      Thanks in advance.

                                       

                                      BR

                                      Kriss

                                        • Re: Joining data field in calculation from two tables
                                          Stefan Wühl

                                          From the Help:

                                           

                                          lookup(fieldname, matchfieldname, matchfieldvalue [, tablename])

                                          Returns the value of fieldname corresponding to the first occurrence of the value matchfieldvalue in the field matchfieldname.

                                          Fieldname, matchfieldname and tablename must be given as strings (e.g. quoted literals).

                                          The search order is load order unless the table is the result of complex operations such as joins, in which case the order is not well defined.

                                          Both fieldname and matchfieldname must be fields in the same table, specified by tablename. If tablename is omitted the current table is assumed.

                                          If no match is found, null is returned.

                                           

                                          Example:

                                          lookup('Price', 'ProductID', InvoicedProd, 'pricelist')

                                           

                                           

                                          In your setting you need to use 'ProductID' resp. ProductID for matchfieldname and matchfieldvalue, fieldname is 'StandardCost' and your tablename is 'Product'.

                                            • Re: Joining data field in calculation from two tables

                                              Hi Swuehl,

                                               

                                              thank you very much for this syntax reference!

                                               

                                              The code with lookup() works and I am able to calculate the value.

                                              Thanks again.

                                               

                                               

                                              - Would you mind assisting in one more question on Concatenation?

                                              I am having trouble creating a new table where I can see the datafields from a concatenation of two tables..

                                              I try naming it according to text in "help" text but not sure that I am doing it right.

                                              - should the original tables (Sales) (quota) already be loaded into QV, logical table ?

                                               

                                              -- then do I use "select" of fields from (Sales) into "new table" with concatenate of fields from (quota) table...

                                               

                                              How do I write this code - to get the new table visible in Script editor.. is it possible?

                                               

                                              BR

                                              Kristina

                                                • Re: Joining data field in calculation from two tables
                                                  Stefan Wühl

                                                  Kristina,

                                                   

                                                  QV will automatically concatenate a table to another if these two tables having exactely the same amount and named fields.

                                                   

                                                  Tab1:

                                                  LOAD

                                                  A, B

                                                  from Table1.xls;

                                                   

                                                  LOAD

                                                  A, B

                                                  from Table2.xls;

                                                   

                                                  resulting in one table Tab1.

                                                   

                                                  You can also force concatenation

                                                   

                                                  Tab1:

                                                  LOAD

                                                  A, B

                                                  from Table1.xls;

                                                   

                                                  CONCATENATE (Tab1) LOAD

                                                  A

                                                  from Table2.xls;

                                                   

                                                  also resulting in one table Tab1, Field B only set with values for records loaded from Table1.xls.

                                                   

                                                  This is mostly it. Instead of loading from Excel files, you can of course reference resident tables.

                                                   

                                                  Hope this helps,

                                                  Stefan