Storing QVDs by date

[#1]

Hello guys,

I try to store source data which are many excel spreadsheets into separate QVD files based on year of their creation. Following should be achieved:

1 QVD = 1 Year from all excel sheets

I have following code:

Sub LoadDistinctYears

Years:

Load Distinct

[Year]

from $(vPathXLS)\CR_*.xlsx // load all XLS 2013, 2014 and so on

(ooxml, embedded labels, table is [INPUT ]);

MinMaxYears:

Load

max ([Year]) as MaxYear,

min ([Year]) as MinYear

Resident Years;

Drop table Years;

ENDSUB;

Call LoadDistinctYears;

LET vMaxYear = FieldValue( 'MaxYear', 1 );

LET vMinYear = FieldValue( 'MinYear', 2 );

Sub LoadXLSData

for i = 2013 to $(vMaxYear)

CR_CORE_DATA_ALL:

LOAD

     All columns

     FROM $(vPathXLS)\CR_*.xlsx (ooxml, embedded labels, table is [INPUT])  // loads CR_2013, 2014 and so on

          where [Year]=$(i);

STORE CR_CORE_DATA_ALL into $(vPathQVDStore) CR_DATA_$(i).qvd;

next i

ENDSUB;

Call LoadXLSData;

What this currently does is storing data into QVDs year by year, but every year has duplicate data in it even also for year previous.

So 2013.qvd has all data for year 2013 but 2014.qvd has data for 2013 and for 2014.

I would really like to see your input on this one. I guess I am wrong in part - (for i = .. to )

Thanks,

          James

[Clever_Anjos · Accepted solution]

Sub LoadXLSData

for i = 2013 to $(vMaxYear)

CR_CORE_DATA_ALL:

LOAD

     All columns

     FROM $(vPathXLS)\CR_*.xlsx (ooxml, embedded labels, table is [INPUT])  // loads CR_2013, 2014 and so on

          where [Year]=$(i);

STORE CR_CORE_DATA_ALL into $(vPathQVDStore) CR_DATA_$(i).qvd;

DROP TABLE CR_CORE_DATA_ALL

next i

[#3]

Excellent Anjos !

Perfectly working now.

Thank you,

          James