How can i convert this SQL query into a qlik script ?

[aresb]

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? 

[maheshkuttappa]

. 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;

[Aditya_Chitale]

@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

[PrashantSangle]

test:

Load * Inline [

date

202101

202102

202103

202104

202105

202106

202107

202108

202109

];

NoConcatenate

temp:

Load date as date1

Resident test

where date>='202101' and date<='202106'

;

Join

Load date as date2

Resident test

where date>='202101' and date<='202106'

;

NoConcatenate

Final:

Load date1,

date2

Resident temp

where date1 >= date2

;

Drop tables test,temp;

Exit Script;