JOIN ORDER

[Anonymous]

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??

[hic · Accepted solution]

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

[Anonymous]

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??

[hic]

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