Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
alec1982
Specialist II
Specialist II

incremental load based on file modified time

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

5 Replies
krishnacbe
Partner - Specialist III
Partner - Specialist III

alec1982
Specialist II
Specialist II
Author

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

krishnacbe
Partner - Specialist III
Partner - Specialist III

Hi,

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

Regards

Krishnapriya

alec1982
Specialist II
Specialist II
Author

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;

erikzions
Creator
Creator

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.