Looping to generate QVD

[Hann]

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;

[stevedark · Accepted solution]

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]

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