Extracting data for last 3 years from SAP in Qlikview using Loops

[Aspiring_Developer]

Hi All,

Please help me with the below:-

I am fetching the data from SAP to Qlikview . Requirement is:-

-Fetch the data for last 3 yearmonths (201703 to 202006) and (202006 should be dynamic ) using loop:-

I have done below :-

for VYearMonth= 201703 to 202006

Load 

Select ...

Variables (
[!V000004] (I = [0CALMONTH].[$(VYearMonth)])),
From (ZOE_MP01/ZOE_MP01_Q005_OS));

next VYearMonth

I want the result to be dynamic for 202006 and post storing the table into QVD, we have to split the QVD's for each year month like 201703 qvd, 201702 qvd;

Can anyone please help ?

@Kushal_Chawda @sunny_talwar 

[Kushal_Chawda · Accepted solution]

@Aspiring_Developer  try below

sub MonthlyLoad (_vStart,_End)

let vMin = MonthStart(date#(_vStart,'YYYYMM'));
let vMax = MonthStart(date#(_End,'YYYYMM'));

Months:
LOAD date(AddMonths('$(vMin)',IterNo()-1),'YYYYMM') as Months
AutoGenerate 1
while AddMonths('$(vMin)',IterNo()-1)<= '$(vMax)';

for i=1 to FieldValueCount('Months')

let vYearMonth = FieldValue('Months',$(i));

Data:
Select ...

Variables (
[!V000004] (I = [0CALMONTH].[$(vYearMonth)])),
From (ZOE_MP01/ZOE_MP01_Q005_OS));

Store Data into C:\$(vYearMonth).qvd; // change your QVD path

DROP Table Data;

NEXT

DROP Table Months;

ENDSUB

CALL MonthlyLoad (201703,202006)

[Aspiring_Developer]

Thank you @Kushal_Chawda for your response

Call MonthlyLoad (201703,202006) is static

When the data will come for 202007, 202008, we will have to Change it manually ?. Can we make it dynamic ?

Please help me to understand

[Kushal_Chawda]

@Aspiring_Developer  we can do that but those values should be stored somewhere so that qlik can pickup from there. It could be in text file or table

[Aspiring_Developer]

@Kushal_Chawda 

I tried one approach for dynamic value but got stuck, could you please help :-

for year = year(today()-3) to year(today()) (Will take year 2017 to 2020)

for period = 1 to 12

I am trying to append year and period in order to create format like 201701, 201702, 201703...202006

But not able to find out how ?

If i manage to create such structure , i can store it in a variable and pass the same in query.

Variables (
[!V000004] (I = [0CALMONTH].[$()])),
From (ZOE_MP01/ZOE_MP01_Q005_OS));

next year

next period

Could you please help  pleasee 

@Anonymous 

[Kushal_Chawda · Accepted solution]

@Aspiring_Developer  try below

let vMin = addyears(yearstart(Today()),-3);
let vMax = monthstart(Today());

Months:
LOAD date(AddMonths('$(vMin)',IterNo()-1),'YYYYMM') as Months
AutoGenerate 1
while AddMonths('$(vMin)',IterNo()-1)<= '$(vMax)';

for i=1 to FieldValueCount('Months')

let vYearMonth = FieldValue('Months',$(i));

Data:
Select ...

Variables (
[!V000004] (I = [0CALMONTH].[$(vYearMonth)])),
From (ZOE_MP01/ZOE_MP01_Q005_OS));

Store Data into C:\$(vYearMonth).qvd; // change QVD path here

DROP Table Data;

NEXT

DROP Table Months;