topic Re: Extracting data for last 3 years from SAP in Qlikview using Loops in App Development

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

Aspiring_Developer — Sat, 16 Nov 2024 18:01:49 GMT

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

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

Kushal_Chawda — Thu, 01 Oct 2020 09:08:24 GMT

@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)

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

Aspiring_Developer — Thu, 01 Oct 2020 09:29:29 GMT

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

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

Kushal_Chawda — Thu, 01 Oct 2020 09:47:20 GMT

@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

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

Aspiring_Developer — Thu, 01 Oct 2020 09:58:37 GMT

@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

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

Kushal_Chawda — Thu, 01 Oct 2020 10:29:12 GMT

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