Split one big QVD file into multiple

[#1]

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

[IAMDV]

Muralidhar Koti - Have you considered QVD Conditional Load?

[#3]

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

[hector · Accepted solution]

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

next

Goog luck & Rgds

[#5]

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

next

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

Hope this helped!

Stefan

[Miguel_Angel_Baeyens]

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.

[danielrozental]

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