Incremental load with Fullaload on every Sunday

[Anonymous]

Hi,

I need help with incremental load script.

Requirement is to implement incremental load on a daily basis with full loads on every Sunday.

Can someone help me with the script.

Thanks!

[Anonymous]

If you have not done an Incremental Load before, then you may find this blog post useful.

http://www.quickintelligence.co.uk/qlikview-incremental-load/

[maxgro]

if you search in the QlikView help (look for QVD) you can find "Using QVD Files for incremental load" and copy/adapt some of the logic/code. If you have some problem post your script and the problem here.

Regarding Sunday, you can use an if with

num(WeekDay( Today() ))          --> result is 0, 1, ....6,  0 on Monday

to switch from incremental to full .

[Anonymous]

I see people are using peek function in incremental loads, can someone please explain me why?

[Anonymous]

I would guess to find the record with the latest date to then load records later than this date.

[Anonymous]

could you please draft that into a script for me? Thanks!

[stabben23]

Hi, something like this:

LET vExecTime = timestamp(vnow, 'YYYYMMDD'); //hh:mm:ss:
SET vLastExecTime= 0; // resetting vLastExecTime

if not isnull(QVDCreateTime('$(QVDPath)MainTable.qvd')) then
LoadTime:
load Maxstring(timeStmp) as X

from $(QVDPath)MainTable.qvd (qvd);

Let vLastExecTime =peek('X',0,'LoadTime');
Drop Table LoadTime;
end if

MainTable:

LOAD
*,
KEY as %key
;

SQL SELECT *
FROM Table
WHERE timeStmp >= '$(vLastExecTime)' and timeStmp <= '$(vExecTime)'  
;

//Check to see if this is the first reload. If it is, skip this step
if Not isnull(QvdCreateTime('$(QVDPath)MainTable.qvd')) then
Concatenate (MainTable)
LOAD *
FROM $(QVDPath)MainTable.qvd (qvd)
Where not (Exists(%key));
end if

//Check to see if there is deleted rows, and full reload
if(WeekDay(today())='sun') then
inner join
Load *, KEY as %key;
SQL SELECT *, KEY
FROM Table
;
end if

//If data exists within table, store to QVD.
if NoOfRows('MainTable') > 0 then
STORE MainTable INTO $(QVDPath)MainTable.qvd (qvd);
Drop Table MainTable;
end if

;