3 Replies Latest reply: Jan 31, 2014 2:20 AM by Henric Cronström RSS

    JOIN ORDER

    Robert Fishel

      Hello my fellow QlikView friends   Today's question is about JOIN ORDER; specifically, the SQL statement below and how to best recode this portion in QV.

       

      FROM ((map_AXmode RIGHT JOIN ((map_calendar RIGHT JOIN qry_AX_LN_1 ON map_calendar.Date = qry_AX_LN_1.[Last goods receipt date]) LEFT JOIN map_ln_origin ON qry_AX_LN_1.[Orig Name] = map_ln_origin.LN_Origin) ON map_AXmode.AX_mode = qry_AX_LN_1.[Inbound Shipment Mode]) LEFT JOIN qry_ibcost_byPO_3 ON qry_AX_LN_1.[ASN PO] = qry_ibcost_byPO_3.[ASN PO]) LEFT JOIN qry_sourcing_3 ON qry_AX_LN_1.[BLANKET ORDER] = qry_sourcing_3.[AX PO#];

       

      The way that I have re-written this, in a nutshell, is by doing the following:

       

      qry_AX_LN_2_map:

       

      //map_AXmode

      LOAD

      RESIDENT map_AXmode;

       

      RIGHT JOIN(qry_AX_LN_2_map)

       

      //map_calendar

      LOAD

      RESIDENT map_calendar;

       

      RIGHT JOIN(qry_AX_LN_2_map)

       

      //qry_AX_LN_1

      LOAD  

      RESIDENT qry_AX_LN_1;

       

      LEFT JOIN (qry_AX_LN_2_map)

       

      //map_ln_orig

      LOAD

      RESIDENT map_ln_orig;

       

      LEFT JOIN (qry_AX_LN_2_map)

       

      //qry_ibcost_byPO_3

      LOAD ASN_PO, //KEY

        POSTED_BYPO_YN

      RESIDENT qry_ibcost_byPO_3;

        

      LEFT JOIN (qry_AX_LN_2_map)

       

      //qry_sourcing_3

      LOAD

      RESIDENT qry_sourcing_3;

       

      DROP TABLES map_AXmode, map_calendar, qry_AX_LN_1, map_ln_orig, qry_ibcost_byPO_3, qry_sourcing_3;

       

      I'm not getting nearly enough records though.  It appears that the main table is qry_AX_LN_1.  Any thoughts??

        • Re: JOIN ORDER
          Henric Cronström

          First of all, in QlikView, you should not join when you don't need to. You can most likely load your tables as separate tables, just making sure that the keys are named the same and that all other fields aren't. See also http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/12/to-join-or-not-to-join

           

          Secondly, if you want to reduce the number of records, you can use "Left Keep" instead. Then you should start with the table that defines the data domain - maybe a fact table with an appropriate Where-clause.

           

          HIC

            • Re: JOIN ORDER
              Robert Fishel

              Thank you Henric.  Now I am wondering if there are a good set of rules to go by for 1) in this case use JOINS, 2) in this case use ApplyMap and 3) in this case, use nothing at all??

                • Re: JOIN ORDER
                  Henric Cronström

                  It is difficult to formulate general rules for data modelling, but in principle you should use a reasonably normalized model with many tables. You need to denormalize slightly, e.g. entities that are used in multiple roles need to be loaded multiple times. So, start by drawing your model using paper and pencil...

                   

                  Then you will find that you might need ApplyMap() or Join to create this model.

                   

                  Finally, you may need to denormalize even more, to gain performance: A snowflake scheme with more than one very large table is often not performant enough.

                   

                  HIC