topic Split one big QVD file into multiple in QlikView

Split one big QVD file into multiple

Tue, 19 Apr 2011 15:05:29 GMT

Hi,

I have one big QVD file (Policy.QVD) with 50 fields. I would like to split this file in to multiple QVD files.

The QVD file has a field Policy Year and I want to create one QVD file per policy year. As I have 10 years data in my QVD, I would except to see 10 different QVD files. Each QVD file should be named as Policy<PolicyYear>.qvd.

Could any one please let me how can I do this in QlikView?

Regards,

Murali

Split one big QVD file into multiple

IAMDV — Tue, 19 Apr 2011 16:15:12 GMT

Muralidhar Koti - Have you considered QVD Conditional Load?

Split one big QVD file into multiple

Tue, 19 Apr 2011 16:20:13 GMT

DV - I think conditional load means placing a where clause during the QVD creation. If my understanding is correct, then YES I did tried it. But in this way I have to execute my script 10 times by changing the where caluse and QVD file name.

Is there any way (loop or automation) where I can automate this?

Regards,

Murali

Split one big QVD file into multiple

hector — Tue, 19 Apr 2011 16:31:53 GMT

Hi, maybe something like this

For vYear = 2000 to 2010 //this can be change by an "for each" clause

MainTable:
Load
fields....
from master.qvd(qvd)
where
FieldYear = $(vYear);

Store MainTable into Policy_$(vYear),qvd;
drop table MainTable; //this is only to avoid the autoconcatenation feature

Goog luck & Rgds

Split one big QVD file into multiple

Tue, 19 Apr 2011 16:37:20 GMT

Hi Muralidhar,

the loop will look like this:

for each Year in '2001','2002','2003','2004'

Policy:

Load * from Policy.qvd (qvd) where PolicyYear = $(Year);

Store Policy Into Policy$(Year).qvd

For more info about loops check "For each .. next" and "For .. next" in QV help

Hope this helped!

Stefan

Split one big QVD file into multiple

Miguel_Angel_Baeyens — Tue, 19 Apr 2011 16:39:03 GMT

Hello,

The "for each" sample that will work even when non consecutive years (I wrote it before but it wasn't posted):

AllPossibleYearsTable:LOAD Chr(39) & CONCAT(DISTINCT "Policy Year", Chr(39) & Chr(44) & Chr(39)) & Chr(39) AS PossibleYearsFROM LargeFile.qvd (qvd); LET vAllPossibleYears = FieldValue('AllPossibleYearsTable', 1); DROP TABLE AllPossibleYearsTable; FOR EACH vYear IN $(vAllPossibleYears) CurrentYear: LOAD * FROM LargeFile.qvd (qvd) WHERE "Policy Year" = $(vYear); STORE CurrentYear INTO Policy$(vYear).qvd; DROP TABLE CurrentYear;NEXT

The first CONCAT load and the play with variables is because FOR EACH needs a comma separated list of values. I've quoted them (chr(39)) but you may not need it, since if variables are numeric, they don't need to be quoted.

Hope that helps.

Split one big QVD file into multiple

danielrozental — Tue, 19 Apr 2011 17:26:46 GMT

I would recommend a few minor changes over Miguel's code if you have large volume.

First, I will just do a For over all years regardless of whether you have info or not, just delete empty files afterwards, also use exists instead of where so loads will be optimized.

FOR vYear = 2000 to 2011

AuxYear:

LOAD $(vYear) AS [Policy Year]

autogenerate(1);

CurrentYear: LOAD * FROM LargeFile.qvd (qvd) WHERE exists([Policy Year]); STORE CurrentYear INTO Policy$(vYear).qvd; DROP TABLE CurrentYear;

DROP TABLE AuxYear;NEXT