To split dates on QVD and move remaining to separate QVD

[Anonymous]

I Have a QVD which has Accounting Period column contains a list of 2 years data  MMYYYY(i.e;012015)

How to write logic to remove all old data (more then 6 months old)from the current Month-Year to previous 6 month QVDs

and move them into Seperate QVD.

[Anonymous]

load your qvd and filter the required months by using where and then store into a new qvd.

[rubenmarin]

Hi, you can create a variable to filter querys to qvd and store the result table in different qvds...

LET vAccountingPeriod = Num(Month(AddMonths(Today(), -6)), '00) & Year(AddMonths(Today(), -6));

OldData:

LOAD * FROM QvdName.qvd(qvd) Where [Accounting Period]<$(vAccountingPeriod);

Store OldData into OldData.qvd(qvd);

DROP Table OldData;

CurrentData:

LOAD * FROM QvdName.qvd(qvd) Where [Accounting Period]>=$(vAccountingPeriod);

Store CurrentData into CurrentData.qvd(qvd);

DROP Table CurrentData;

[sushil353]

Hi,

1. first of all get the max period from existing qvd file..

2. then get the past 6 month date like:

=Date(AddMonths(date(Date#('012015','MMYYYY')),-6) ,'MMYYYY')

3. filter your data from qvd using the condition and store it in new qvd

[Anonymous]

Sorry previously not clear about my requirement.

step-1:we are getting QVD from Application which has Accounting Period like as  (YYYYMM as 201408) there are a Number of Years like 2013,2014,2015

step 2:How to write concatenate of all 2013,2014,2015 years into 1 QVD and How to Split for separate Individual QVD as 2013.qvd,2014.qvd,2015.qvd

Step 3:How to get 6 months of data into separate QVD from Accounting Period (201408 i.e.,YYYYMM)

let say From current month Year to get Different combinations of QVD in if else logic

All Accounting Period QVD

Yearly QVD

Past 6 months Qvd

[rubenmarin]

OK, not tested but it should be something like:

Step-1: Load all accounting period qvd

AllData:

LOAD * From qvdName.qvd(qvd);

Step-2: Generate qvds for each year

Years_tmp:

LOAD Distinct Year as YearToFilter

Resident AllData Order By Year;

LET vMinYear = Peek('YearToFilter', 0, 'Years_tmp');

LET vMaxYear = Peek('YearToFilter', NoOfRows('Years_tmp')-1, 'Years_tmp');

For vYearToFilter = vMinYear to vMaxYear

     TempData:

     LOAD * Resident AllData WhereYear=$(vYearToFilter);

     STORE TempData into $(vYearToFilter).qvd(qvd);

     DROP Table TempData;

NEXT

Step-3: Generate qvd with data of last 6 months

LET vAccountingPeriod = Num(Month(AddMonths(Today(), -6)), '00) & Year(AddMonths(Today(), -6));

TempData:

LOAD * Resident AllData Where [Accounting Period]>=$(vAccountingPeriod);

STORE TempData into Past6Months.qvd(qvd);

DROP Table TempData;

DROP Table AllData;

[Anonymous]

Hi,

Try below code:

All Accounting Period QVD:

TempTable:
Load Min(YearField) as minYear, max(YearField) as maxYear;
LOAD Distinct Year([Accounting Period]) AS YearField
FROM QVD\Main.qvd(qvd)
Order by Year([Accounting Period]) asc;

Let vMin=Peek('minYear', 0, 'TempTable');
Let vMax=Peek('maxYear', 0, 'TempTable');

FOR i=$(vMin) to $(vMax)
LET vYear = Peek(' YearField ',$(i),'TempTable');
 
ConsolidateYearWise:
Concatenate
LOAD * ,
Year([Accounting Period]) AS YearFlag
FROM QVD\Main.qvd(qvd)
Where Year([Accounting Period]) = '$(vYear)';

Next

STORE ConsolidateYearWise into ConsolidateData.qvd;

DROP Table ConsolidateYearWise;

DROP Table TempTable;

Yearly QVD:
    
TempTable:
Load Min(YearField) as minYear, max(YearField) as maxYear;
LOAD Distinct Year([Accounting Period]) AS YearField
FROM QVD\Main.qvd(qvd)
Order by Year([Accounting Period]) asc;

Let vMin=Peek('minYear', 0, 'TempTable');
Let vMax=Peek('maxYear', 0, 'TempTable');

FOR i=$(vMin) to $(vMax)
LET vYear = Peek(' YearField ',$(i),'TempTable');
 
SeparateYearWise:
NoConcatenate
LOAD * FROM QVD\Main.qvd(qvd)
Where Year([Accounting Period]) = '$(vYear)';

STORE SeparateYearWise into TableName_$(vYear).qvd;
DROP Table SeparateYearWise;

Next

DROP Table TempTable;

Past 6 months QVD:

LET vAccountingPeriod = num(addmonths(today(),-6));

SixMonthsData:
LOAD * FROM QVD\Main.qvd(qvd)
Where [Accounting Period]>=$(vAccountingPeriod);

STORE SixMonthsData into TableName_SixMonthsData.qvd;
DROP Table SixMonthsData;

Regards

Neetha

[Anonymous]

Hi Neetha thanx for your solutions but not able to get desired results solution

TempTable:==>Here you shown Min(YearField) as minYear, max(YearField) as maxYear; not able to write syntax throwing Invalid Expression Syntax

Load Min(YearField) as minYear, max(YearField) as maxYear;==>whether we need to load using any Resident table

LOAD Distinct Year([Accounting Period]) AS YearField
FROM QVD\Main.qvd(qvd)  Order by Year([Accounting Period]) asc; 

Let vMin=Peek('minYear', 0, 'TempTable');

Let vMax=Peek('maxYear', 0, 'TempTable');

Not able to get vMin,vMax

Any solution on this

TempTable:

LOAD Distinct PeriodYear AS YearField

//Min(PeriodYear) as minYear,

//max(PeriodYear) as maxYear

//Order by Year(AccPrdOGIS) asc;

//Resident OGIS;

Resident OGIS Order By PeriodYear asc;

//Let vMin=Peek('minYear', 0, 'OGISTable');

//Let vMax=Peek('maxYear', 0, 'OGISTable');

[Anonymous · Accepted solution]

//Try below code:
//All Accounting Period QVD:

TempTable:
LOAD Distinct Year([Accounting Period]) AS YearField
FROM QVD\Main.qvd(qvd)
Order by Year([Accounting Period]) asc;

NoConcatenate

Table:
Load YearField,Min(YearField) as minYear, max(YearField) as maxYear
Resident TempTable
Group By YearField;

Let vMin=Peek('minYear', 0, 'Table');
Let vMax=Peek('maxYear', 0, 'Table');

FOR i=$(vMin) to $(vMax)
LET vYear = Peek(' YearField ',$(i),'TempTable');

ConsolidateYearWise:
Concatenate
LOAD * ,
Year([Accounting Period]) AS YearFlag
FROM QVD\Main.qvd(qvd)
Where Year([Accounting Period]) = '$(vYear)';

Next

STORE ConsolidateYearWise into ConsolidateData.qvd;

DROP Table ConsolidateYearWise;

DROP Table TempTable;
DROP Table Table;


//Yearly QVD:

TempTable:
LOAD Distinct Year([Accounting Period]) AS YearField
FROM QVD\Main.qvd(qvd)
Order by Year([Accounting Period]) asc;

NoConcatenate

Table:
Load YearField,Min(YearField) as minYear, max(YearField) as maxYear
Resident TempTable
Group By YearField;

Let vMin=Peek('minYear', 0, 'Table');
Let vMax=Peek('maxYear', 0, 'Table');

FOR i=$(vMin) to $(vMax)
LET vYear = Peek(' YearField ',$(i),'TempTable');

SeparateYearWise:
NoConcatenate
LOAD * FROM QVD\Main.qvd(qvd)
Where Year([Accounting Period]) = '$(vYear)';

STORE SeparateYearWise into TableName_$(vYear).qvd;
DROP Table SeparateYearWise;

Next

DROP Table TempTable;
DROP Table Table;


//Past 6 months QVD:

LET vAccountingPeriod = num(addmonths(today(),-6));

SixMonthsData:
LOAD * FROM QVD\Main.qvd(qvd)
Where [Accounting Period]>=$(vAccountingPeriod);

STORE SixMonthsData into TableName_SixMonthsData.qvd;
DROP Table SixMonthsData;

[Anonymous]

TempTable:

//Tried Year,monthstart,monthens,Quarter

LOAD Distinct Year(AccPrdOGIS) AS YearField,

  date(date#(AccPrdOGIS, 'YYYYMM')) as PeriodStartMonth,

    date(floor(MonthEnd(date#(AccPrdOGIS, 'YYYYMM')))) as PeriodEndMonth,

    year(date#(AccPrdOGIS, 'YYYYMM')) as PeriodYear,

    ceil(month(date#(AccPrdOGIS, 'YYYYMM'))/3) as PeriodQuarter,

    year(date#(AccPrdOGIS, 'YYYYMM')) & ceil(month(date#(AccPrdOGIS, 'YYYYMM'))/3) as PeriodYearQuarter 

FROM

(qvd)

Order by Year(AccPrdOGIS) asc;

EXIT Script;

Tried below code showing as Garbage value After statement Tried Year,monthstart,monthens,Quarter.

Can you help on what am missing after Order By