Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone
i have this query from a sql table, which is a master calendar:
select
distinct SUBSTRING(A.id_date,1,6) as M1,
SUBSTRING(B.id_date,1,6) as M2
from
edp_l1.tbedpl1iom_date_m A,edp_l1.tbedpl1iom_date_m B
where
SUBSTRING(A.id_date,1,6) BETWEEN 202101 and 202106
and SUBSTRING(A.id_date,1,4) = SUBSTRING(B.id_date,1,4)
and SUBSTRING(A.id_date,1,6) >= SUBSTRING(B.id_date,1,6)
This query produce this statement
202101 202101
202102 202101
202102 202102
202103 202101
202103 202102
202103 202103
202104 202101
202104 202102
202104 202103
202104 202104
202105 202101
202105 202102
202105 202103
202105 202104
202105 202105
202106 202101
202106 202102
202106 202103
202106 202104
202106 202105
202106 202106
Now , i'd like to replicate this in qlik. I have a master calendar qvd, and i use this script
[TMP_CAL]:
LOAD
Distinct cod_year_month as M1,
cod_year_month as M2
FROM [lib://PATH/Calendar.qvd]
(qvd);
NoConcatenate
[CAL]:
LOAD
M1,M2
RESIDENT [TMP_CAL]
WHERE(M1 >= '202101' AND M2 <= '202106') AND
LEFT(M1,4) = LEFT(M2,4)
AND M1 >= M2 ;
drop Table [TMP_CAL];
but the output is this
What am i doing wrong ?
Does anyone have any advice?
. Try this, or if it doesn't work post id_date data
A:
Load
mid(id_date,3,6) as M1,
mid(id_date,3,4) as M1_Key
Resident Data where mid(id_date,3,6) >= '202101' AND mid(id_date,3,6) <= '202106';
Left join (A)
Load
M1 as M2, M1_Key
Resident A;
drop table Data;
Final:
Load
M1,M2
Resident A
where M1 >= M2;
Drop Table A;
@aresb ,
You are loading the same field 2 times as M1 & M2 from the qvd (In your case below field:)
cod_year_month
and on top of that you are taking distinct entries. So it is obvious that you will get only single entry per row.
In your sql code M1 & M2 dates are fetched from 2 different tables and there might be Multiple M2 dates against single M1 date hence the condition in sql is working properly. Make sure you have multiple M2 dates in your QVD as well for single M1 date.
Regards,
Aditya