Hi There
I am working on an Incremental load. I can sort of get it working and it shows the files are compiling but then once compiled, it crashes at the last minute saying to load old data etc.
Can anyone see where I am going wrong.
I have multiple tables that are going to do more or less the same thing.
Premise is: the incremental looks for the last 6 months of data and brings that data in.
Any help appreciated.
////////////////////////////////////////
//// Incremental Load //////////////////
////////////////////////////////////////
if ('$(vIncremental)' = 'Y') then
// Load all data except for the last 6 months.
Logs:
LOAD
*
FROM
[QVDs\Logs.qvd](qvd)
// max date < todays date
where floor(DATE(max(Logs_Date), 'YYYY-MM')) < floor(date(addMonths(Today(), -6), 'YYYY-MM'));
// in case there is an error, the following line will leave the script run without stopping
set ErrorMode = 0;
Let vNumMonths = FieldValueCount('Last6MonthsDate');
if $(vNumMonths) > 0 then
TRACE File to get: $(vNumMonths);
for i=0 to $(vNumMonths)-1
//Let vFileMonth = Peek('Last6MonthsDate', $(i), 'Last6MonthsTable');
Let vMonthFile = Date(Date#(Peek('Last6MonthsDate', $(i), 'Last6MonthsTable'), 'YYYY-MM'), 'YYYYMM');
Let j = $(i)+1;
TRACE vMonthFile: $(vMonthFile);
TRACE Record: $(j) of $(vNumMonths) - $(vMonthFile);
//Load Logs table
Logs:
LOAD
A, B,C,D,E,
date(DATE#(mid(filename(),7,6), 'YYYYMM'), 'YYYY-MM') as Logs_Date,
right(left(FileName(),5),2) as Logs_Country_Code
FROM
[CE_*$(vMonthFile)*_LOGS_*.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
if ScriptError <> 0 then
TRACE $(ScriptErrorDetails);
ErrorTable:
LOAD
Now() as theDate,
'$(vMonthFile)' & 'Logs*.csv' as theFilename,
'File Not Found' as errorDetail,
'Logs' as ScriptTab
AutoGenerate(1);
ENDIF
NEXT
ENDIF
set ErrorMode = 1;
ELSE
//perform full load
Logs:
LOAD
A,B,C,D,E,
date(DATE#(mid(filename(),7,6), 'YYYYMM'), 'YYYY-MM') as Logs_Date,
right(left(FileName(),5),2) as Logs_Country_Code
FROM
[CE_*LOGS_*.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq) ;
ENDIF
Store Logs into ..\QVDs\Logs.qvd (qvd);
DROP Table Logs;
exit script
