topic Re: How to extract period Wise Qvds in QlikView

How to extract period Wise Qvds

Fri, 04 Sep 2015 12:59:00 GMT

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‌,

Re: How to extract period Wise Qvds

Sun, 06 Sep 2015 10:42:46 GMT

Please any One can help me on this

Re: How to extract period Wise Qvds

Anonymous — Sun, 06 Sep 2015 10:48:58 GMT

hi john,

Try:

PeriodWise(Past specific months) QVD Creation

Re: How to extract period Wise Qvds

maxgro — Sun, 06 Sep 2015 18:09:41 GMT

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;

Re: How to extract period Wise Qvds

Mon, 07 Sep 2015 04:52:28 GMT

Thank you Neetha.

Re: How to extract period Wise Qvds

Mon, 07 Sep 2015 04:53:17 GMT

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

Re: How to extract period Wise Qvds

Mon, 14 Sep 2015 06:29:42 GMT

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