topic Re: Left join using between in App Development

Left join using between

DebP — Mon, 20 Dec 2021 21:32:42 GMT

Hi All,

New to Qlik and a very basic question to ask.

How to achieve the following in Qlik load script.

select a.*, b.X from table1

left join table2

where table1 .A = table2 .A

and a.time between b.starttime and b.endtime

Thanks.

Re: Left join using between

Taoufiq_Zarra — Sat, 07 Mar 2020 16:02:20 GMT

can you share a sample data from table A and B to propose the exact solution

Re: Left join using between

DebP — Sun, 08 Mar 2020 10:39:30 GMT

Hi, please see the load script below. Getting 4 records in the final table, whereas expected is 2 records. Thanks

FCT_TRANSACTION:
LOAD * Inline [
TRANSACTION_NO, CONTRACT_CD, TRANSACTION_DT
10000,PM1,12/10/2018
20000,PM1,12/10/2019
];

PRODUCT_GROUP:
LOAD * Inline [
CONTRACT_CD, FROM_DATE, TO_DATE,PRODUCT_GROUP_ID
PM1,1/1/2018,12/31/2018, 100
PM1,1/1/2019,1/1/2099,101
PM2,1/1/2019,1/1/2099,102
];

Left Join (FCT_TRANSACTION)
LOAD
* Resident PRODUCT_GROUP;

left JOIN (FCT_TRANSACTION)
LOAD TRANSACTION_DT,
FROM_DATE as F,
TO_DATE as T
RESIDENT FCT_TRANSACTION
WHERE CONTRACT_CD=CONTRACT_CD and TRANSACTION_DT >= FROM_DATE AND TRANSACTION_DT < TO_DATE
;
drop table PRODUCT_GROUP;
drop fields FROM_DATE,TO_DATE;
rename field F to FROM_DATE;
rename field T to TO_DATE;
exit script;

Re: Left join using between

Taoufiq_Zarra — Sun, 08 Mar 2020 11:10:40 GMT

maye be thisi :

FCT_TRANSACTION: LOAD * Inline [ TRANSACTION_NO, CONTRACT_CD, TRANSACTION_DT 10000,PM1,12/10/2018 20000,PM1,12/10/2019 ]; left join PRODUCT_GROUP: LOAD * Inline [ CONTRACT_CD, FROM_DATE, TO_DATE,PRODUCT_GROUP_ID PM1,1/1/2018,12/31/2018, 100 PM1,1/1/2019,1/1/2099,101 PM2,1/1/2019,1/1/2099,102 ]; Result: Noconcatenate load * resident FCT_TRANSACTION Where Date(TRANSACTION_DT)>=Date(FROM_DATE) and Date(TRANSACTION_DT)<=Date(TO_DATE); drop table FCT_TRANSACTION;

Re: Left join using between

DebP — Sun, 08 Mar 2020 15:17:07 GMT

Thanks it worked.

But, if i add additional row which is not present in the following PRODUCT_GROUP then in the result table this record will be missed. I need all 3 records in the result table.

FCT_TRANSACTION:
LOAD * Inline [
TRANSACTION_NO, CONTRACT_CD, TRANSACTION_DT
10000,PM1,12/10/2018
20000,PM1,12/10/2019
30000,PM7,12/10/2019
];
left join
PRODUCT_GROUP:
LOAD * Inline [
CONTRACT_CD, FROM_DATE, TO_DATE,PRODUCT_GROUP_ID
PM1,1/1/2018,12/31/2018, 100
PM1,1/1/2019,1/1/2099,101
PM2,1/1/2019,1/1/2099,102
];
Result:
Noconcatenate
load * resident FCT_TRANSACTION
Where Date(TRANSACTION_DT)>=Date(FROM_DATE) and Date(TRANSACTION_DT)<=Date(TO_DATE);
drop table FCT_TRANSACTION;

exit script;

Re: Left join using between

Taoufiq_Zarra — Mon, 09 Mar 2020 09:32:42 GMT

I didn't quite get it, bu maye be :

FCT_TRANSACTION: LOAD * Inline [ TRANSACTION_NO, CONTRACT_CD, TRANSACTION_DT 10000,PM1,12/10/2018 20000,PM1,12/10/2019 30000,PM7,12/10/2019 ]; left join PRODUCT_GROUP: LOAD * Inline [ CONTRACT_CD, FROM_DATE, TO_DATE,PRODUCT_GROUP_ID PM1,1/1/2018,12/31/2018, 100 PM1,1/1/2019,1/1/2099,101 PM2,1/1/2019,1/1/2099,102 ]; Result: Noconcatenate load * resident FCT_TRANSACTION Where (Date(TRANSACTION_DT)>=Date(FROM_DATE) and Date(TRANSACTION_DT)<=Date(TO_DATE)) or (not isnull(CONTRACT_CD) and (isnull(TO_DATE) and isnull(FROM_DATE))); drop table FCT_TRANSACTION;