Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
@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 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;
@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)
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
@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
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
@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;