Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
DebP
Contributor
Contributor

Left join using between

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.

5 Replies
Taoufiq_Zarra

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
DebP
Contributor
Contributor
Author

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;

 

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
DebP
Contributor
Contributor
Author

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;

 

 

Capture.PNG

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉