topic Re: To split dates on QVD and move remaining to separate QVD in QlikView

To split dates on QVD and move remaining to separate QVD

Anonymous — Thu, 08 Jan 2015 09:56:01 GMT

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.

Re: To split dates on QVD and move remaining to separate QVD

Anonymous — Thu, 08 Jan 2015 09:58:01 GMT

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

Re: To split dates on QVD and move remaining to separate QVD

rubenmarin — Thu, 08 Jan 2015 10:03:24 GMT

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;

Re: To split dates on QVD and move remaining to separate QVD

sushil353 — Thu, 08 Jan 2015 10:04:54 GMT

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

Re: To split dates on QVD and move remaining to separate QVD

Anonymous — Thu, 08 Jan 2015 15:16:24 GMT

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

Re: To split dates on QVD and move remaining to separate QVD

rubenmarin — Thu, 08 Jan 2015 16:06:45 GMT

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;

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;

Re: To split dates on QVD and move remaining to separate QVD

Anonymous — Thu, 08 Jan 2015 16:31:24 GMT

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

Re: To split dates on QVD and move remaining to separate QVD

Anonymous — Mon, 12 Jan 2015 11:46:58 GMT

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');

Re: To split dates on QVD and move remaining to separate QVD

Anonymous — Mon, 12 Jan 2015 11:58:10 GMT

//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;

Re: To split dates on QVD and move remaining to separate QVD

Anonymous — Mon, 12 Jan 2015 12:23:35 GMT

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

Re: To split dates on QVD and move remaining to separate QVD

Anonymous — Mon, 12 Jan 2015 12:27:56 GMT

from Temp.qvd(qvd)

AccPrdOGIS consists of YYYYMM Format like 201501

Re: To split dates on QVD and move remaining to separate QVD

its_anandrjs — Mon, 12 Jan 2015 13:17:13 GMT

Hi,

For Yearly Qvd creation you can get help from this Document this creates Yearly QVD.

Year Wise Qvd creation from Source Data

Regards

Anand

Re: To split dates on QVD and move remaining to separate QVD

Anonymous — Mon, 12 Jan 2015 15:08:09 GMT

Hi,

Try:

LOAD Distinct Year(date#(AccPrdOGIS,'YYYYMM')) 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(date#(AccPrdOGIS,'YYYYMM')) asc;

EXIT Script;