load last 18 months data qlik sense script

[cedfoning]

hello, i have an issue

i have the following table in a qvd file

Test:

id_cle, cd_mois_alim

1, 198001,

2, 199003, 

3, 201707

4,202101

5,202105

6,202106

7,202107

8,202201

9,202202

10,202203

11,202204

12,202205

13,202206

i want the load the above table with a condition on a date cd_mois_alim. 
I want to have the data as from 202101 till now. 
Knowing that when we will have 202207, it will be automatic, keeping the date of beginning 202101. 

thanks for your help 

@vinieme12 

[Vegar]

I didn't really understand the details of your logic, but I believe the key to your problem is to make Qlik intepret cd_mois_alim as a date.

Try something like this.

LOAD id_cle, cd_mois_alim

FROM DataSource

WHERE Date#(cd_mois_alim, 'YYYYMM') >= AddMonths(Date#(cd_mois_alim, 'YYYYMM'),-17)

[vinieme12]

Alternatively , you can also use

To Always load Last 18 Month

Test:
Load
id_cle, cd_mois_alim
Froum SomeSource;

Inner Join(Test)
Load Date(monthstart(today(),-recno()+1),'YYYYMM') as cd_mois_alim
Autogenerate 18;

Using Exists

KeepDates:
Load Date(monthstart(today(),-recno()+1),'YYYYMM') as cd_mois_alim
Autogenerate 18;

Test:
Load
id_cle, cd_mois_alim
Froum SomeSource
Where Exists(cd_mois_alim);
Drop table KeepDates;

To Always load from Previous Year 1 Jan

Test:
Load
id_cle, cd_mois_alim
Froum SomeSource;

Inner Join(Test)
Load Date(monthstart(today(),-recno()+1),'YYYYMM') as cd_mois_alim
Autogenerate ((year(today())*12)+month(today())) -  (((year(today())-1)*12)+1) + 1 ;

Using Exists()

KeepDates:
Load Date(monthstart(today(),-recno()+1),'YYYYMM') as cd_mois_alim
Autogenerate ((year(today())*12)+month(today())) -  (((year(today())-1)*12)+1) + 1 ;

Test:
Load
id_cle, cd_mois_alim
Froum SomeSource
Where Exists(cd_mois_alim);
Drop Table KeepDates;

[cedfoning]

Hello, 

i will always want to load data as from 202101. 
in 202306 for exemple, il will load data as from 202101 to 202306
in 202207, i will load data from 202101 to 202207
in 202208, i will load data as from 202101 to 202208

etc

i think with your method it is ,not possible

thanks

[MayilVahanan]

HI

Try like below

Load id_cle, cd_mois_alim from ursource where cd_mois_alim >= 202101;

so, it will load always from 202101

[vinieme12]

i've already provided the solution for that scenario above

You can use either of the two

To Always load from Previous Year 1 Jan

Test:
Load
id_cle, cd_mois_alim
Froum SomeSource;

Inner Join(Test)
Load Date(monthstart(today(),-recno()+1),'YYYYMM') as cd_mois_alim
Autogenerate ((year(today())*12)+month(today())) -  (((year(today())-1)*12)+1) + 1 ;

Using Exists()

KeepDates:
Load Date(monthstart(today(),-recno()+1),'YYYYMM') as cd_mois_alim
Autogenerate ((year(today())*12)+month(today())) -  (((year(today())-1)*12)+1) + 1 ;

Test:
Load
id_cle, cd_mois_alim
Froum SomeSource
Where Exists(cd_mois_alim);
Drop Table KeepDates;

[cedfoning]

Yes, i saw that 
but, consider we are in 202306
if i apply this solution, it will load data as from 202201  right ? 
and if we are in 202406, it will load data as from 202301. 

Each time, i want to load data as from 202101

[vinieme12 · Accepted solution]

you can simply change the highlighted part

Autogenerate ((year(today())*12)+month(today())) - ((2021*12)+1) + 1 ;

Test:
Load
id_cle, cd_mois_alim
Froum SomeSource;

Inner Join(Test)
Load Date(monthstart(today(),-recno()+1),'YYYYMM') as cd_mois_alim
Autogenerate ((year(today())*12)+month(today())) -  ((2021*12)+1) + 1 ;