topic Re: Generation of QVD's by Loop in QlikView

Generate QVD's for each 5 years

nihhalmca — Mon, 03 Nov 2014 09:32:44 GMT

Hi All,

I want to generate multiple Qvd's by single load.

Ex: i have data from 1990 to current year (2014).

i want to generate multiple qvd's by perform single load.

QVD 1 should have data from 1990 to 1994

QVD 2 should have data from1995 to 1999

QVD 3 should have data from 2000 to 2004

QVD 4 should have data from 2005 to 2009

QVD 5 should have data from 2010 to 2014.

Please help me, how can write for loop.

Regards,

Nihhal.

Re: Generation of QVD's by Loop

simondachstr — Mon, 03 Nov 2014 09:37:59 GMT

Can you specify from where you want to load the data?

Re: Generation of QVD's by Loop

simenkg — Mon, 03 Nov 2014 09:38:02 GMT

for x = 1 to 20

'QVD$(x)to$(=x+4)':

load *

from Source

where Year >= $(=1990+x) and Year <= (1990 + x + 4);

Store 'QVD$(x)to$(=x+4)' into 'QVD$(x)to$(=x+4)'.qvd (qvd);

drop table 'QVD$(x)to$(=x+4)';

let x = x+4;

next x

Re: Generation of QVD's by Loop

nihhalmca — Mon, 03 Nov 2014 09:48:51 GMT

excel file

Re: Generation of QVD's by Loop

stevedark — Mon, 03 Nov 2014 10:14:29 GMT

If there are multiple Excel files, and you want a QVD for each of them this article may be of use:

http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

Simen's code will work if it is all in one file, but I believe you can lose the +4 at the end and add a step statement to the for loop.

Steve

Re: Generation of QVD's by Loop

nihhalmca — Mon, 03 Nov 2014 10:23:34 GMT

Source is single file. it has data from 1990 to 2014.

Re: Generation of QVD's by Loop

stevedark — Mon, 03 Nov 2014 10:26:21 GMT

What is the rationale behind multiple QVDs then? This will slow the load

process considerably as the Excel file will need to be parsed for each four

year period.

Steve

Re: Generation of QVD's by For Loop

nihhalmca — Mon, 03 Nov 2014 10:33:41 GMT

Hi Steve,

I need to generate every 5 years as 1 QVD.

I tried, however i have generated 4 qvds by single load but Qvds has full data.

Re: Generation of QVD's by For Loop

jagan — Tue, 04 Nov 2014 08:07:23 GMT

Hi,

Try this sample script

Temp:

LOAD

1990 + RecNo() - 1 AS Year

AutoGenerate 24;

for x = 1 To 5

LET vEndYear = vStartYear + 4;

LET vFileName = 'QVD' & ($(vStartYear)) & 'to' & ($(vEndYear)) ;

[$(vFileName)]:

NoConcatenate

load *

Resident Temp

where Year >= $(vStartYear) and Year <= $(vEndYear);

Store $(vFileName) into $(vFileName).qvd (qvd);

drop table $(vFileName);

LET vStartYear = vStartYear + 5;

next x

Note : Just replace Temp table with your actual data.

Hope this helps you.

Regards,

Jagan.