topic Re: Incremental Load in QlikView

Incremental Load

bobbydave — Thu, 16 Apr 2015 11:05:19 GMT

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

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

Re: Incremental Load

Anonymous — Thu, 16 Apr 2015 12:31:07 GMT

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.

Re: Incremental Load

bobbydave — Thu, 16 Apr 2015 12:44:26 GMT

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 (qvd)

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.

Re: Incremental Load

buzzy996 — Thu, 16 Apr 2015 12:44:35 GMT

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.

Re: Incremental Load

Anonymous — Thu, 16 Apr 2015 12:49:15 GMT

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.

Re: Incremental Load

jonathandienst — Thu, 16 Apr 2015 13:13:54 GMT

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

Re: Incremental Load

bobbydave — Thu, 16 Apr 2015 15:00:58 GMT

I believe my QVD was corrupted so I deleted it. Recompiled and its not failing now.

Thanks for your advice