How to split a large QVD into Quarters per year ?

[#1]

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!

[fosuzuki]

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;

next

[santharubban]

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?.