6 Replies Latest reply: Aug 27, 2009 2:30 AM by John Witherspoon RSS

    JOIN PROBLEM

      Dear all,

      I am a new user in the field of BI and particularly in QV. I started my learning curve with the task to upgrade and refine a "monstrous" report created by QV expert who has gone now. Few things I would like to ask:

      1) Any special quidance for type of joins would be greatly appreciated. I have tried the well known from SQL left-right-inner-outer and they do not behave exactly the way I expect.

      2) Is there any good book for learning Qlikview ?

      What causes me a confusion is basically the way QV makes its own connections between the tables. I cannot undestand fully how dow this process work.

      In the report I mention, there are about 25 keys selfdefined by QV when organising data. I tried to add one additional key-field and the system got crashed. Is it possible to LOAD data but to tell QV to keep it aside and not to try merging it with eveything else?

      Thank in advance for any help.

      Kalin

        • AW:JOIN PROBLEM
          driesena

          QlikView connects tables between columns having exactly the same names.

          If you do not want QV to link two tables, rename the corresponding columns with "AS".

          Or try this at the beginning of the script:

          QUALIFY *;

          This will load all columns with the leading table name in front, e.g.: "Table1.Column1" instead of "Column1".

          But this will change all existing colum names in the application as well! So you have to change all formulas, listboxes and charts... ;-)

          And you have to "unqualify" the keys, which you want to use for linking the tables:

          e.g.: UNQUALIFY '%*';

          Otherwise the tables won't be linked anymore.

          Therefore you have to rename the key columns like this:

          LOAD bla AS %bla

          FROM...

           

          Greets,

          Andreas

            • AW:JOIN PROBLEM
              driesena
                • AW:JOIN PROBLEM

                  Thanks I will try ASAP to see this "Cookbook"

                    • AW:JOIN PROBLEM
                      John Witherspoon

                      Left, right, inner and outer joins should behave correctly. That said, we generally aren't trying to join ALL data into a single mammoth table. Instead, we're doing more implied joins by simply naming fields the same.

                      You could think of it as a relational database system, where one table has the key to another table. So I might have an order item with a "customer" field on it. I also have a customer table with "customer" as the key.

                      In SQL, I might grab related data from both tables by doing this:

                      SELECT O.OrderItem, O.OrderQuantity, C.Customer, C.CustomerName
                      FROM Orders O
                      , Customers C
                      WHERE C.CUSTOMER = O.CUSTOMER

                      In QlikView, we CAN do that:

                      [Order Items and Customers]:
                      LOAD *
                      ;
                      SQL
                      SELECT O.OrderItem, O.OrderQuantity, C.Customer, C.CustomerName
                      FROM Orders O
                      , Customers C
                      WHERE C.Customer = O.Customer
                      ;

                      But I more typically load tables with separate SQL, and let QlikView handle the connection on Customer. Our QlikView table layout then mimics our relational database table layout. That isn't required to make QlikView work, but may be convenient for helping you see the connections between it and your source data.

                      [Order Items]:
                      LOAD *
                      ;
                      SQL
                      SELECT OrderItem, OrderQuantity, Customer
                      FROM Orders
                      ;
                      [Customers]:
                      LOAD *
                      ;
                      SQL
                      SELECT Customer, CustomerName
                      FROM Customers
                      ;

                      Or even if I want them to be one table in QlikView, I'll typically do the join inside of QlikView instead of SQL, partially because I usually use QVDs that are often (though not always) 1 to 1 with our source tables.

                      [Order Items and Customers]:
                      LOAD *
                      ;
                      SQL
                      SELECT OrderItem, OrderQuantity, Customer
                      FROM Orders
                      ;
                      INNER JOIN ([Order Items and Customers])
                      LOAD *
                      WHERE EXISTS(Customer)
                      ;
                      SQL
                      SELECT Customer, CustomerName
                      FROM Customers
                      ;

                        • AW:JOIN PROBLEM
                          Mahasweta

                          Hi John,

                          Really thanks a lot for your post. It gives idea regarding joins in QV.

                          I have some of the doubts.

                          As you have mentioned we can write the same sql query in QV window

                          [Order Items and Customers]:
                          LOAD *
                          ;
                          SQL
                          SELECT O.OrderItem, O.OrderQuantity, C.Customer, C.CustomerName
                          FROM Orders O
                          , Customers C
                          WHERE C.Customer = O.Customer
                          ;

                          If we write the script this way will it affect performance, as in the script it tries to join sql tables and then fetches data. So will this be ideal?

                          And i am facing some issue with where exists caluse.

                          like unknown statement.my script is as below

                          [tab1]:
                          select VALUE as VDLPS,"LOCATION_NUM","PRODUCT_NUM","MODEL_NUM","SCENARIO_NUM" from ADEXAUSER."TEST_ADR_ACT_DLPS";

                          //tab2:
                          //select VALUE as VCDLPS,"SCENARIO_NUM" from ADEXAUSER."TEST_ADR_ACT_CDLPS";
                          inner join [tab1]
                          where exists ("SCENARIO_NUM","MODEL_NUM");
                          select VALUE as VCDLPS,"SCENARIO_NUM" from ADEXAUSER."TEST_ADR_ACT_CDLPS";

                          Could you please let me know where am doing wrong?

                          Thanks,

                          Mahasweta

                            • AW:JOIN PROBLEM
                              John Witherspoon

                              As long as the join is efficient for your DBMS, it will be efficient for QlikView to load in the results. Whether it is the ideal approach or not depends on far too many things for me to really say. If it works and doesn't bog down, I'd say you're in good shape.

                              As for the problem in your script, you need a load statement for each of these. I like doing the renames in QlikView rather than in SQL. I believe you need parentheses around the table name in the inner join, the format for exists is exists(current field, field being loaded). Since I don't know your data, I don't know if you want to just reverse the fields, or if you want both fields to be SCENARIO_NUM, which can be abbreviated as exists(SCENARIO_NUM). And you have to tell QlikView that you're doing SQL before it can process the select. So I think someting like this (untested):

                              [tab1]:
                              LOAD
                              VALUE as VDLPS
                              ,LOCATION_NUM
                              ,PRODUCT_NUM
                              ,MODEL_NUM
                              ,SCENARIO_NUM
                              ;
                              SQL SELECT
                              VALUE
                              ,LOCATION_NUM
                              ,PRODUCT_NUM
                              ,MODEL_NUM
                              ,SCENARIO_NUM
                              FROM ADEXAUSER."TEST_ADR_ACT_DLPS"
                              ;
                              INNER JOIN ([tab1])
                              LOAD
                              VALUE as VCDLPS
                              ,SCENARIO_NUM
                              WHERE EXISTS(SCENARIO_NUM)
                              ;
                              SQL SLECT
                              VALUE
                              ,SCENARIO_NUM
                              FROM ADEXAUSER."TEST_ADR_ACT_CDLPS"
                              ;

                              But it does look like this might be easier with the join and renames in SQL:

                              LOAD *
                              ;
                              SQL SELECT
                              DLPS.LOCATION_NUM
                              ,DLPS.PRODUCT_NUM
                              ,DLPS.MODEL_NUM
                              ,DLPS.SCENARIO_NUM
                              ,DLPS.VALUE AS VDLPS
                              ,CDLPS.VALUE AS VCDLPS
                              FROM ADEXAUSER."TEST_ADR_ACT_DLPS" DLPS
                              , ADEXAUSER."TEST_ADR_ACT_CDLPS" CDLPS
                              WHERE CDLPS.SCENARIO_NUM = DLPS.SCENARIO_NUM
                              ;

                              I don't normally do it that way, but I probably would in this case.