Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
//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;
load your qvd and filter the required months by using where and then store into a new qvd.
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;
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
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
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;
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
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');
//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;
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