How to extract period Wise Qvds

[#1]

Hi All,

I have following code which is generating monthly qvds for every year. however I want to generate period wise qvds for each year.

every year has 13 periods, each period has 28 days, and first period starts at May 25th and  last period ends at 24 May next year.

say P1 starts at  25 May-2015

      P13 ends at  24- May-2016

can any one help me on this how to get it.

LET vmindate= num(AddYears(num(Today()),-3));

Sales:
LOAD Rand()+24 as Sales,
     Date(Date,'MM/DD/YYYY') AS Date;
LOAD $(vmindate)+RecNo()-1 as Date
AUTOGENERATE num(Today())-$(vmindate);

LET vcurrentdate= num(MonthStart(AddYears(num(Today()),-3))); // Set start date

DO WHILE vcurrentdate <= Today()

   ExportSales:
   NOCONCATENATE
   LOAD *
   RESIDENT Sales
   WHERE InMonth(Date, $(vcurrentdate), 0) = true();

   LET vdatestring = Date($(vcurrentdate), 'YYYY_MMM');
   STORE ExportSales INTO [..\QVDS\Incrementalqvds\$(vdatestring).QVD] (qvd);
   DROP Table ExportSales;

   LET vcurrentdate = num(AddMonths($(vcurrentdate), 1));
  
LOOP

Please help me on this swuehl‌ jagan‌, gwassenaar‌, hic‌ tresesco‌,

[#2]

Please any One can help me on this

[Anonymous]

hi john,

Try:

PeriodWise(Past specific months) QVD Creation

[maxgro · Accepted solution]

SET DateFormat='DD/MM/YYYY';

// test data

source:

load

  date(makedate(2014) + rowno() -1) as date,

  Floor(Rand()*100) as val

AutoGenerate 365*3+1;

// elab

tmp:

load min(date) as min, max(date) as max Resident source;

let vMin=(Peek('min'));

let vMax=(Peek('max'));

let delta1='$(vMin)'-floor(YearStart('$(vMin)'));

let delta2=MakeDate(2015,05,25) - floor(YearStart(MakeDate(2015,05,25)));

if (  $(delta1) >= $(delta2)  ) then

       let vYear=Year($(vMin));

ELSE

       let vYear=Year($(vMin))-1;

ENDIF;

TRACE vMin=$(vMin) vMax=$(vMax) - delta1=$(delta1) delta2=$(delta2) - year=$(vYear);

FOR y=$(vYear) to Year($(vMax))

  FOR p=1 to 13

       TRACE;

       TRACE ********** elab $(y) $(p);

       IF $(p)=1 then

            LET dstart=num(makedate($(y), 5, 25));

       ELSE

            LET dstart=$(dend)+1;

       ENDIF;

       IF $(p)=13 then

            // some years the last period doesn't end (with +28) on 24, hard code it

            LET dend=num(MakeDate(Year($(dstart)),5,24));

       ELSE

            LET dend=$(dstart)+28-1; 

       ENDIF;

       let ddstart=date(dstart);

       let ddend=date(dend);

       TRACE y=$(y) p=$(p);

       TRACE ddstart=$(ddstart) ddend=$(ddend);

       table_$(y)_$(p):

       NoConcatenate load * Resident source

       where date>=$(dstart) and date<=$(dend);

       LET vrows=NoOfRows('table_$(y)_$(p)');

       TRACE vrows=$(vrows);

       IF $(vrows)>0 then

            TRACE STORE table_$(y)_$(p) into table_$(y)_$(p).qvd (qvd);

            STORE table_$(y)_$(p) into table_$(y)_$(p).qvd (qvd);

       ENDIF;

       DROP Table table_$(y)_$(p);

  NEXT;

NEXT;

[#5]

Thank you Neetha.

[#6]

Thanks Massimo Grossi it is working.

// some years the last period doesn't end (with +28) on 24, hard code it. 

Here for every year  we are taking 29 days or 30 for last period, can we restrict these period values to 13 for last period up to 5 years and then the remaining 1 or 2 days of last 5 years do we add as a 5 week for the  6th year? . however Period 1 has to start at  May25 and P13 has to end at 24 May, is this possible?

Best Regards
John

[#7]

Hi Maxgro,

here the period loop is running up to 13 periods  for the Current years also, however can we restrict the loop up to todays date. if yes how do we restrict it?

Regards

John