topic Re: Split the qvd file and generate partition qvd files in QlikView

Split the qvd file and generate partition qvd files

Wed, 14 Mar 2012 08:37:27 GMT

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

Split the qvd file and generate partition qvd files

jvitantonio — Wed, 14 Mar 2012 08:50:09 GMT

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;

Split the qvd file and generate partition qvd files

CELAMBARASAN — Wed, 14 Mar 2012 08:57:49 GMT

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

Re: Split the qvd file and generate partition qvd files

Wed, 14 Mar 2012 09:49:26 GMT

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;

Split the qvd file and generate partition qvd files

CELAMBARASAN — Wed, 14 Mar 2012 10:17:09 GMT

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;

DROP Table Temp;

Celambarasan

Re: Split the qvd file and generate partition qvd files

Wed, 14 Mar 2012 11:31:01 GMT

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;

DROP Table Temp;

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

Split the qvd file and generate partition qvd files

CELAMBARASAN — Wed, 14 Mar 2012 11:58:24 GMT

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

Re: Split the qvd file and generate partition qvd files

Wed, 14 Mar 2012 12:01:21 GMT

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.

Split the qvd file and generate partition qvd files

CELAMBARASAN — Wed, 14 Mar 2012 12:03:59 GMT

Hi,

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

Celambarasan

Re: Split the qvd file and generate partition qvd files

Wed, 14 Mar 2012 12:14:39 GMT

Ok thanks to Share KT to me.