Storing QVDs by date

Thu, 05 Jun 2014 16:56:55 GMT

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

Re: Storing QVDs by date

Clever_Anjos — Thu, 05 Jun 2014 17:10:29 GMT

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

Re: Storing QVDs by date

Fri, 06 Jun 2014 07:58:35 GMT

Excellent Anjos !

Perfectly working now.

Thank you,

James

topic Storing QVDs by date in QlikView

Storing QVDs by date

Re: Storing QVDs by date

Re: Storing QVDs by date