Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Is that first load intended to get all the log dates to load (excluding any in the last 6 months)?
If so, then all you need should be:
// Load all data except for the last 6 months.
Logs:
LOAD * FROM [QVDs\Logs.qvd](qvd)
Where Logs_Date < addMonths(Today(), -6);
Later on you intermix Peeking from some table and using FieldValue. Use one or the other, not both together. Both the order and the number of records could differ. For getting the number of records for the Peek, use NoOfRows('Table').
HTH
Jonathan
Could you share the log file from the load ?
Or at a wild guess try putting a sleep(5000) between the Store and the DROP.
I guess it doesnt like this line of text
where floor(DATE(max(Logs_Date), 'YYYY-MM'),day(today())) < floor(date(addMonths(Today(), -6), 'YYYY-MM'))
16/04/2015 13:36:25: 0017 let vIncremental = 'Y'
16/04/2015 13:36:25: 0018 set ErrorMode = 0
16/04/2015 13:36:25: 0062 let v1stMonth = date(addmonths(today(),-6),'YYYY-MM')
16/04/2015 13:36:25: 0063 let v2ndMonth = date(addmonths(today(),-5),'YYYY-MM')
16/04/2015 13:36:25: 0064 let v3rdMonth = date(addmonths(today(),-4),'YYYY-MM')
16/04/2015 13:36:25: 0065 let v4thMonth = date(addmonths(today(),-3),'YYYY-MM')
16/04/2015 13:36:25: 0066 let v5thMonth = date(addmonths(today(),-2),'YYYY-MM')
16/04/2015 13:36:25: 0067 let v6thMonth = date(addmonths(today(),-1),'YYYY-MM')
16/04/2015 13:36:25: 0068 let v7thMonth = date(today(),'YYYY-MM')
16/04/2015 13:36:25: 0072 Last6MonthsTable:
16/04/2015 13:36:25: 0073 LOAD * INLINE [
16/04/2015 13:36:25: 0074 Last6MonthsDate
16/04/2015 13:36:25: 0075 2014-10
16/04/2015 13:36:25: 0076 2014-11
16/04/2015 13:36:25: 0077 2014-12
16/04/2015 13:36:25: 0078 2015-01
16/04/2015 13:36:25: 0079 2015-02
16/04/2015 13:36:25: 0080 2015-03
16/04/2015 13:36:25: 0081 2015-04
16/04/2015 13:36:25: 0082 ]
16/04/2015 13:36:25: 1 fields found: Last6MonthsDate, 7 lines fetched
16/04/2015 13:36:25: 0128 if ('Y' = 'Y') then
16/04/2015 13:36:25: 0132 Logs:
16/04/2015 13:36:25: 0133 LOAD
16/04/2015 13:36:25: 0134 *
16/04/2015 13:36:25: 0135 FROM
16/04/2015 13:36:25: 0136
16/04/2015 13:36:25: 0137
16/04/2015 13:36:25: 0138
16/04/2015 13:36:25: 0139 where floor(DATE(max(Logs_Date), 'YYYY-MM'),day(today())) < floor(date(addMonths(Today(), -6), 'YYYY-MM'))
16/04/2015 13:36:25: General Script Error
16/04/2015 13:36:25: Execution Failed
16/04/2015 13:36:25: Execution finished.
I Guess there is some problem to fetch ur old data and map the new data with ur old data.
there is no issue on ur above code.
I have seen that kind of error when the qvd in question is locked by something.
Check whether you have any zombie qv.exe processes running on your machine and if so kill them.
Or a blunt way to clear any locks is to reboot the machine.
Is that first load intended to get all the log dates to load (excluding any in the last 6 months)?
If so, then all you need should be:
// Load all data except for the last 6 months.
Logs:
LOAD * FROM [QVDs\Logs.qvd](qvd)
Where Logs_Date < addMonths(Today(), -6);
Later on you intermix Peeking from some table and using FieldValue. Use one or the other, not both together. Both the order and the number of records could differ. For getting the number of records for the Peek, use NoOfRows('Table').
HTH
Jonathan
I believe my QVD was corrupted so I deleted it. Recompiled and its not failing now.
Thanks for your advice