topic How to split a large QVD into Quarters per year ? in QlikView

How to split a large QVD into Quarters per year ?

Thu, 26 Jun 2014 16:45:58 GMT

Hi,

I have a large qvd with data from 2010-2014 and I want to split it into 4Quarters per year and store them in separate qvds. There is a timestamp (created) in datetime format available. I was wondering if i could use the quarterName() function but i am not sure how to proceed. Thx for your suggestions!

Re: How to split a large QVD into Quarters per year ?

fosuzuki — Thu, 26 Jun 2014 18:01:50 GMT

Hi, check the sample code below.

//Generate a sample fact table with dates from 2010/01/01 to 2014/12/31

SampleData:

LOAD RecNo() AS ID,

Date(MakeDate(2010,1,1) + recno() - 1) AS Date

AutoGenerate(1826);

//Get all possible quarters and lower/upper date limits

Quarters:

LOAD Year(Date) & '-Q' & ceil(Month(Date)/3) AS Quarter,

Min(Date) AS MinDate,

Max(Date) As MaxDate

Resident SampleData

Group By Year(Date) & '-Q' & ceil(Month(Date)/3);

//Loop table Quarters

for i = 0 to NoOfRows('Quarters') - 1

let vQuarterName = Peek('Quarter', $(i));

let vMinDate = Peek('MinDate', $(i));

let vMaxDate = Peek('MaxDate', $(i));

SampleDataByQuarter:

NoConcatenate

LOAD *

Resident SampleData

Where Date >= $(vMinDate) AND Date <= $(vMaxDate);

STORE SampleDataByQuarter into SampleData_$(vQuarterName).qvd (qvd);

DROP Table SampleDataByQuarter;

Re: How to split a large QVD into Quarters per year ?

santharubban — Wed, 10 May 2017 20:32:46 GMT

Hi Suzuki,

After splitting the QVD's ,How do we load the data with these QVD's ? or can you please tell the logic to how pull the data from these QVD's?.