topic Re: incremental load based on file modified time in QlikView

incremental load based on file modified time

alec1982 — Wed, 25 Nov 2020 16:16:04 GMT

hi,

I have incremental load set up on a set of files to only load new modified files(each file has one row of data).

the issue is the time to incrementally load the the information in files is the same as full load because the incremental load checks every single file if the date is new..

Anyone has a better solution to optimize the load..

thxs,

alec

Re: incremental load based on file modified time

krishnacbe — Tue, 12 Apr 2016 15:42:39 GMT

Please check below links

http://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Incremental load in Qlikview

Re: incremental load based on file modified time

alec1982 — Tue, 12 Apr 2016 15:47:27 GMT

hi,

thank you for your quick reply. the links explains how to build incremental load nicely. but that is not my question.

In my case, I have the incremental load logic built but looking for a solution to optimize it as it takes the same amount of time as a full load as explained in my question.

Best,

Alec

Re: incremental load based on file modified time

krishnacbe — Tue, 12 Apr 2016 16:14:49 GMT

Hi,

Can you explain some more in detail on how you have implemented the Incremental Load.

Regards

Krishnapriya

Re: incremental load based on file modified time

alec1982 — Tue, 12 Apr 2016 16:22:48 GMT

sure, here is my script:

SET vEDXTriggersQVD='S:\Workspace\Harfoush.Badr\EDXTaskSchedules.qvd';

Let vFileExsist=if(FileSize('$(vEDXTriggersQVD)')>0,-1,0);

if $(vFileExsist) then

Temp:

load

max(TimeStamp) as MaxTimeStampLoaded

FROM

[$(vEDXTriggersQVD)]

(qvd);

LET vMaxTimeStampLoaded = peek('MaxTimeStampLoaded');

drop table Temp;

EDXTaskSchedules:

Load * from $(vEDXTriggersQVD);

Concatenate(EDXTaskSchedules)

LOAD ExecId,

TaskId as TaskID,

TaskStatus,

TaskLogFilePath,

StartTime,

FinishTime,

filetime() as TimeUpdated,

// Num(Filetime()) as TimeStampNum,

//num(Date(DATE#(filetime(),'MM/DD/YYYY h:mm:ss TT'),'MM/DD/YYYY')) as TimeStampNum2,

Num(DATE#(Filetime(),'MM/DD/YYYY h:mm:ss TT')) as TimeStamp

FROM [$(vQDSPath)EDXResult\EDXResult_*.xml] (XmlSimple, Table is [Root])

where Filetime()>timestamp('$(vMaxTimeStampLoaded)');

Else

EDXTaskSchedules:

LOAD ExecId,

TaskId as TaskID,

TaskStatus,

TaskLogFilePath,

StartTime,

FinishTime,

filetime() as TimeUpdated,

Num(DATE#(Filetime(),'MM/DD/YYYY h:mm:ss TT')) as TimeStamp

FROM [$(vQDSPath)EDXResult\EDXResult_*.xml] (XmlSimple, Table is [Root]);

End if;

Store EDXTaskSchedules into EDXTaskSchedules.qvd;

Re: incremental load based on file modified time

erikzions — Tue, 12 Apr 2016 16:29:28 GMT

Is it possible to store all that data into a single qvd. It would hold all the meta data and would only have to open a single qvd to check for changes. You could then loop through the table and not each file.