Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Hann
Partner
Partner

Looping to generate QVD

Hi,

I have 2 months daily data and need to store previous 3 day data as current day qvd.

Below is my script. I'm able to generate the 3 day to one qvd file but I want to automate the qvd generation if I have like 2 or 1 month data.

 

Below is my script. But the problem with this script is it keep generate the 1st sept 2020 qvd. I think its because of the minDate variable.

 

LET vMinDate = makedate(2020,09,01);
LET vMaxDate = makedate(2020,09,17);

FOR vCounter = '$(vMinDate)' to '$(vMaxDate)'


// LET vStartDate =addmonths(Today()-3,0); //ori script
// LET vToday =addmonths(Today()-1,0); //ori scritp

LET vStartDate =addmonths('$(vMinDate)'-3,0); //ori script
LET vToday =addmonths('$(vMinDate)'-1,0);

FOR vCounter = '$(vStartDate)' to '$(vToday)'; //loop the file date

LET vFilter = date('$(vCounter)','YYYYMMDD');

Inv_Period:
LOAD
Sales_Date as Inv_Date,
"Item No",
Outlet,
Total_BaseQty,
Total_Amount_Excl_Tax
FROM [lib://Data/T3/POS Detail/T3_POS_Detail_Daily_$(vFilter).qvd]
(qvd);

Next vCounter;

 

Let vDate = Date(floor(NUM('$(vMinDate)')),'YYYYMMDD');
// LET vFilter2 = date('$(vCounter)','YYYYMMDD');
Inv:
Load
*,
Date(floor(NUM('$(vMinDate)')),'DD/MM/YYYY') as Sales_Date;

LOAD
"Item No",
Outlet,
Sum(Total_BaseQty) as Sales_Qty,
Sum(Total_Amount_Excl_Tax) as Sales_Amt
Resident Inv_Period
Group by "Item No", Outlet;

Store Inv into [lib://Data/T3/Inventory Period/Test/T3_Inventory_Period_$(vDate).qvd](qvd);

Drop Table Inv;

next;

 

1 Solution

Accepted Solutions
stevedark
MVP
MVP

Hi @Hann 

I think adding some TRACE statements to the code might certainly help things out here.

I'm surprised that the FOR loops work on string dates, but then I have never tried this, so if the TRACE shows this is behaving as you require then great! I personally would get the dates as integers (e.g. 43653) and use those everywhere (except in the file suffixes).

Your outer loop is putting the date value in a variable called vCounter, but when you set vStartDate and vToday you are getting this from vMinDate, instead of vCounter. This is, I think, why you are always getting the same data in each itteration of the loop.

Also, I think you need to be dropping Inv_Period after you create the Inv table each time, or the Inv_Period table will simply keep growing.

Hopefully that will, at least, move you a bit nearer to the solution you are after.

Cheers,
Steve

 

View solution in original post

2 Replies
stevedark
MVP
MVP

Hi @Hann 

I think adding some TRACE statements to the code might certainly help things out here.

I'm surprised that the FOR loops work on string dates, but then I have never tried this, so if the TRACE shows this is behaving as you require then great! I personally would get the dates as integers (e.g. 43653) and use those everywhere (except in the file suffixes).

Your outer loop is putting the date value in a variable called vCounter, but when you set vStartDate and vToday you are getting this from vMinDate, instead of vCounter. This is, I think, why you are always getting the same data in each itteration of the loop.

Also, I think you need to be dropping Inv_Period after you create the Inv table each time, or the Inv_Period table will simply keep growing.

Hopefully that will, at least, move you a bit nearer to the solution you are after.

Cheers,
Steve

 

Hann
Partner
Partner
Author

Dear Steve,

 

Yes I've completed the app and did just as what you mentioned. Thanks for the feedback, really appreciate it.  I attached the apps here for anyone reference.

 

Regards,

Hannan