Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
can you share a sample data from table A and B to propose the exact solution
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;
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;
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;
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;