Split the qvd file and generate partition qvd files

[#1]

Hello,

I've 6 months of data in my qvd file. Now i want to generate qvd file per month.

For Eg:

If i've Jan, Feb, March, April, May, June months of data in qvd file. I want to generate one qvd file for Jan month, one for Feb, one for March ..... one for june in one application itself.

In my qvd file i've Date field with shows the data in the below format.

     DateColumn

05-01-2012 00:12

06-01-2012 00:12

07-01-2012 00:12

     .

     .

     .

     31-06-2012  12:12:40

[jvitantonio]

Ok, put this on your script:

FOR i = 1 to 6

table:

LOAD * FROM file.qvd(qvd)

where num(Month(DateColumn)) = i

STORE table INTO myfile.qvd;

drop table table;

NEXT

[CELAMBARASAN]

Hi,

     Try with this

FOR i=1 to 12

let vTableName=Month(MakeDate(2011,$(i)));

$(vTableName):

LOAD

          *

Resident TableName where Month(DateField)=$(i);

Store $(vTableName) into ..\QVD\$(vTableName).qvd;

DROP Table $(vTableName);

NEXT i;

Celambarasan

[#4]

Hello,

Your idea is good. But for loop will repeat for 12 times even if those months are not existing. If you see below code it works perfectly but how can i get the existed count of months in a variable. B'coz i don't know how many months are existed in my qvd file. Right now it is 6 later it will be 3 or 4 or 9 or 12.

Temp:

LOAD Distinct Month(CaptureDateTime) AS MonthName

FROM

QVD\RT_Full.qvd

(qvd);

FOR i=0 to 6

LET vMonth = Peek('MonthName',$(i),'Temp');

MonthWise:

LOAD Month(CaptureDateTime) AS MonthName1

FROM

QVD\RT_Full.qvd

(qvd)

Where Month(CaptureDateTime) = '$(vMonth)'

;

STORE MonthWise into Month\$(vMonth).qvd;

DROP Table MonthWise;

Next

DROP Table Temp;

[CELAMBARASAN · Accepted solution]

Hi,

     Try like this

Temp:

LOAD Distinct Month(CaptureDateTime)*1 AS MonthName

FROM

QVD\RT_Full.qvd

(qvd) Order by Month(CaptureDateTime) asc;

Let vMin=Peek('MonthName',0);

Let vMax=Peek('MonthName')

FOR i=$(vMin) to $(vMax)

LET vMonth = Peek('MonthName',$(i),'Temp');

MonthWise:

LOAD Month(CaptureDateTime) AS MonthName1

FROM

QVD\RT_Full.qvd

(qvd)

Where Month(CaptureDateTime) = '$(vMonth)'

;

STORE MonthWise into Month\$(vMonth).qvd;

DROP Table MonthWise;

Next

DROP Table Temp;

Celambarasan

[#6]

Thanks for your help. I also got the solution with below code.

Temp:

LOAD  Distinct Month(CaptureDateTime) AS MonthName

FROM

RTQMain_Full.qvd

(qvd)

;

Let vCount=NoOfRows('Temp');

FOR i=0 to $(vCount)

LET vMonth = Peek('MonthName',$(i),'Temp');

MonthWise:

LOAD *

FROM

RTQMain_Full.qvd

(qvd)

Where Month(CaptureDateTime) = '$(vMonth)'

;

STORE MonthWise into Month\$(vMonth).qvd;

DROP Table MonthWise;

Next

DROP Table Temp;

But my question is which will be faster because i'm using aroung 37GB qvd file.

[CELAMBARASAN]

Hi,

     You  can go with your idea itself using NoOfRows().I didn't look that you have used Distinct for months i formulated the solutions that you can have same month multiple times.

Celambarasan

[#8]

Thanks a lot.. But i want to know whether your code loads fastly. B'coz in where condition i'm comparing with month name and you are comparing with month number.

[CELAMBARASAN]

Hi,

     Yes.Its a well known one handling number is faster that string better use Month number to increase your performance.

Celambarasan

[#10]

Ok thanks to Share KT to me.