topic Re: Incremental load with Fullaload on every Sunday in QlikView

Incremental load with Fullaload on every Sunday

Anonymous — Wed, 18 Nov 2015 18:19:02 GMT

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!

Re: Incremental load with Fullaload on every Sunday

Anonymous — Wed, 18 Nov 2015 18:22:09 GMT

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/

Re: Incremental load with Fullaload on every Sunday

maxgro — Wed, 18 Nov 2015 18:27:18 GMT

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 .

Re: Incremental load with Fullaload on every Sunday

Anonymous — Wed, 18 Nov 2015 18:35:03 GMT

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

Re: Incremental load with Fullaload on every Sunday

Anonymous — Wed, 18 Nov 2015 18:37:58 GMT

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

Re: Incremental load with Fullaload on every Sunday

Anonymous — Wed, 18 Nov 2015 18:44:19 GMT

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

Re: Incremental load with Fullaload on every Sunday

stabben23 — Wed, 18 Nov 2015 19:00:24 GMT

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

;