Load-Join priority

[JohanL74]

Dear Qlik Community,

i am trying to load 4 xls and then load 4 other xls to join them to the first 4. What priority does QlikSense follow?

I made this script:

Orders:

load %Key, Plandate

from Orders Planning week *.xls; (with * representing Planning week P1, P2, AP, P4)

left join (Orders)

load %Key, ActualDate

from Orders Result week *.xls; (with * representing Result week R1, R2, R3, R4)

I understand step 1: Qlik loads P1 till P4 and concatenates them to 1 table since format is always the same.

I have a question about step 2: what will happen?

Scenario 1:

load Result week R1 ---> Join to Orders-table;

load Result week R2 ---> Join to Orders-table;

load Result week R3 ---> Join to Orders-table;

load Result week R4 ---> Join to Orders-table;

Scenario 2: Qlik loads R1 till R4 and concatenates them to 1 table since format is always the same.

load Result week R1 ;

load Result week R2;

load Result week R3;

load Result week R4;

then ---> Join combined R1-R4 to Orders-table;

[Clement15]

Hello,

You can see what is the loading order of Qlik when loading the script or in debug mode by step. But logically, I would say that only one Orders table will be loaded, then the Orders Result Week tables will be loaded and will join each in turn.

[marcus_sommer]

The data seems to be equally. Therefore they shouldn't be joined else concatenated, like:

t:
load %Key, Plandate as Date, 'Plan' as Source from Planning;
concatenate(t) 
load %Key, ActualDate as Date, 'Result' as Source from Result;

[henrikalmen]

If you do want to join tables, and not concatenate them, you should probably do the second step like this:

tmp: noconcatenate load %Key, ActualDate from Orders Result week *.xls; 
left join(orders) load * resident tmp;
drop table tmp;

If the data set is very large you could increase performance by temporarily store the tmp table to a qvd, or if the tmp table actually only has the key field and one (1) other field you could speed things up using a mapping table instead of performing a join.

[rwunderlich]

Your scenario #1 is correct to describe processing order. And it will not give expected results. After the R1 join, other rows on Orders will have null for AcrtualDate, so no subsequent joins will not match. 

Do it the way @henrikalmen suggested, load to a temp table first.

-Rob