Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Excellent Anjos !
Perfectly working now.
Thank you,
James