How To get Rolling 12 months of data from Accounting Period of each Financial QVD

[Anonymous]

Hi Friends,can any one help on getting Rolling 12 months of data from Current Date.Suppose my QVD Generators are generating Accounting Yearwise QVD i.e.,2012.qvd,2013.qvd,2014.qvd.Now i need to use in final Application to get 12 months of data from current .

Eg: Today()-->Dec-2014 need to get Data from 2014.qvd(i.e., 11 months) and 2013.qvd,(i.e., 1 month) like this i need .I need logic in script while loading from  multiple qvd to pick 12 months into Application only not All data

Below is logic written for splitting year wise QVD

temp:
load max([Monthstart_Sales]) as period   Resident $(Sale_Table);
let vperiodcheck=peek('period',0,'temp');
let vlastyear=year(today())-1;
let vCurrentyear=year(today());

if num(month($(vperiodcheck)))=1 then;

TEMP1:
NoConcatenate LOAD * Resident $( Sale_Table)
where [Monthstart_Sales]<YearStart(Today());
store TEMP1 into Sale_previous_$(vlastyear).qvd(qvd);
drop Table TEMP1;
ELSE
TEMP1:
NoConcatenate LOAD * Resident $( Sale_Table)
where [Monthstart_Sales]>=YearStart(Today());
store TEMP1 into Sale_current_$(vCurrentyear).qvd(qvd);
drop Table TEMP1;

Please help on solution as it is Effecting working hours

[datanibbler]

Hi swetha,

you will need a filter so that you know which month's data you need from which (yearly) qvd.

You can use the ADDMONTHS() function to determine the start_date (one year back).

=> Next you'll have to determine the dates (or year-months or whatever is your keyfield) that lie

     inbetween that start_date and today (or the respective MONTHSTART())

=> Then you can use those filters to load only the required data from your yearly qvd_files.

HTH

Best regards,

DataNibbler

[Anonymous]

Hi,

Hope i have understood your question. Here is my take on the solution.

assuming you have a Date field in your dataset.

create a variable called vrunning12months.

LET vrunning12months = Fabs(Today()) - 365;

and while loading the QVD's

LOAD

*

FROM Sale_current_*.qvd(qvd)

WHERE DateField >= $(vrunning12months);

iF You dont have a date field and have only months.

Then create a table with distinct list of Year and Month numbers like 201412, 201411, 201410.. etc..

and then create the same variable with months.

LET vrunning12months = Peek('MonthFieldName', -12, 'MonthFieldTable');

LOAD

*

FROM Sale_current_*.qvd(qvd)

WHERE MonthYearField >= $(vrunning12months);

Hope this helps.

Aadil

[Anonymous]

Above is 2014.qvd,2013.qvd Need to Load 12 months of data from current Month for this Acct-Prd00 created start date and End Date for each month in QVD Generators.Need to check For Each qvd in a loop or If else logic to check

[Anonymous]

Aadil Thanx for your Solution but not working in my case,can you please suggest on best solution.

While loading 2013.qvd, concatenate 2014.qvd to first load into memory

Step 2: Need to Read vPresent 2014.qvd(Let say has 11 months of data) and again it should check 2011.qvd to get 1 month of data in 2013.qvd any help on this

[Anonymous]

Please check attached.

hope it helps.

Aadil

[Anonymous]

Can any please do needful on rolling 12 months of data from current month.here I need to capture current qvd data and need to roll previous years to get exactly 12 months of data.

[jeanjordaan007]

Hi.

check this. It should be what you are looking for.

Rolling 6 month load from multiple QVD

That script can be adjusted for 12/6/8 months.

But why don't you just create the sales table with the data you need.

Something like this would work

let vlastyear=year(today())-1;

let vCurrentyear=year(today());

Sales_Table:

Load

    *

FROM

..\$(vCurrentyear).qvd(qvd)

where num(month([Monthstart_Sales])) <= num(month(Today()));

Concatenate (Sales_Table)

LOAD

  *

FROM

..\$(vlastyear).qvd(qvd)

where num(month([Monthstart_Sales])) >= num(month(Today()));

Unless there is a reason why you are loading all of the data, I can not see why you would want to load all of the data and then only resident extract the data.