Generate QVD's for each 5 years

[nihhalmca]

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.

[simondachstr]

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

[simenkg]

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

[nihhalmca]

excel file

[stevedark]

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

[nihhalmca]

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

[stevedark]

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

[nihhalmca]

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.

[jagan]

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.