Left join using between

[DebP]

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.

[Taoufiq_Zarra]

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

[DebP]

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;

[Taoufiq_Zarra]

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;

[DebP]

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;

[Taoufiq_Zarra]

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;