Left join inside a LOAD in the script

[marcosirotti1950]

I have two tables defined as such:

ORDERS:
Agent OrdID OrderDate
Agt1    1           2021-05-15
Agt1    2           2022-01-01
Agt2 ...
...
SALE_AGENTS:
Agent DateFrom    DateTo          Dept
Agt1   2021-01-01  2021-05-01  Dept1
Agt1   2021-05-02  2021-12-01  Dept2
Agt1   2021-12-02  2022-05-31  Dept3
Agt2 ...
...

I need to join the 2 tables in a way that for every order of every agent I have the dept in wich he was located at the time of the order. In this case I need:

Agent OrdID OderDate      Dept
Agt1    1           2021-05-15  Dept2
Agt1    2           2022-01-01  Dept3
Agt2 ...
...

The two tables are in different connections, so I cannot make a direct SQL JOIN.

How can I do this in a LOAD statement after the two different SQL SELECT ? Can I use in Qlik Sense a left (or inner) join with a where condition based on data from the two different tables? What would be the correct syntax?

Thank you

[vinieme12]

As below

ORDERS:
load Agent,OrdID,date(date#(OrderDate,'YYYY-MM-DD')) as OrderDate inline [
Agent,OrdID,OrderDate
Agt1,1,2021-05-15
Agt1,2,2022-01-01
];

Left JOin(ORDERS)
Load
Agent
,Dept
,DateFrom+iterno() as OrderDate
While DateFrom+iterno()<=DateTo
;
load Agent,Dept,date(date#(DateFrom,'YYYY-MM-DD')) as DateFrom , date(date#(DateTo,'YYYY-MM-DD')) as DateTo inline [
Agent,DateFrom,DateTo,Dept
Agt1,2021-01-01,2021-05-20,Dept1
Agt1,2021-05-20,2022-05-31,Dept2
];

[vinieme12]

you can also use IntervalMatch()

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

ORDERS:
load Agent,OrdID,date(date#(OrderDate,'YYYY-MM-DD')) as OrderDate inline [
Agent,OrdID,OrderDate
Agt1,1,2021-05-15
Agt1,2,2022-01-01
];

SALE_AGENTS:
load Agent,Dept,date(date#(DateFrom,'YYYY-MM-DD')) as DateFrom , date(date#(DateTo,'YYYY-MM-DD')) as DateTo inline [
Agent,DateFrom,DateTo,Dept
Agt1,2021-01-01,2021-05-20,Dept1
Agt1,2021-05-20,2022-05-31,Dept2
];

Left Join IntervalMatch ( OrderDate,Agent )
LOAD DateFrom,DateTo,Agent
Resident SALE_AGENTS;
exit script;

[marcosirotti1950 · Accepted solution]

Thanks, the IntervalMatch solution works fine. I just had to add a distint to the last load.