Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

baarathi
Contributor II

Increamental load

LastUpdatedDate:

Load max(Date("Date of Joining")) as MaxDate

  FROM  lib://QVDs/abc.qvd(qvd);

Let varThisExec = Date(Now());

Let varLastExec = peek('MaxDate', 0, 'LastUpdatedDate');

M:

LOAD

    "First Name",

    "Last Name",

    "Date of Joining",

    Designation,

    Location,

    Salary,

    ID

FROM [lib://QVDs/Employee.xls]

(biff, embedded labels, table is Sheet1$)

Where "Date of Joining" >= '$(varLastExec)';

Concatenate

LOAD

    "First Name",

    "Last Name",

    "Date of Joining",

    Designation,

    Location,

    Salary,

    ID

from lib://QVDs/abc.qvd(qvd)

where not Exists(ID);

if '$(ScriptErrorCount)' = 0 then

Store M into [lib://QVDs/abc.qvd](qvd);

Let varLastExec = '$(varThisExec)';

endif

I was Trying to implement increamental load with insert, update and delete. When I tried with the above script. It's loading the entire data. Instead of Incremental Load. How to correct the above?

1 Solution

Accepted Solutions
MVP
MVP

Re: Increamental load

The Where clause needs a properly formatted date. Modify your script:


LastUpdatedDate:

Load Max("Date of Joining") as MaxDate

  FROM  lib://QVDs/abc.qvd(qvd);

Let varThisExec = Date(Now());

Let varLastExec = Date(peek('MaxDate', 0, 'LastUpdatedDate'));

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
5 Replies
ali_hijazi
Honored Contributor

Re: Increamental load

can you debug and check what '$(varLastExec)' is evaluated to?

I mean check the value of this variable


but I think you need to fix the line:

Let varLastExec = peek('MaxDate', 0, 'LastUpdatedDate');

to

Let varLastExec = date(peek('MaxDate', 0, 'LastUpdatedDate'));

MVP
MVP

Re: Increamental load

The Where clause needs a properly formatted date. Modify your script:


LastUpdatedDate:

Load Max("Date of Joining") as MaxDate

  FROM  lib://QVDs/abc.qvd(qvd);

Let varThisExec = Date(Now());

Let varLastExec = Date(peek('MaxDate', 0, 'LastUpdatedDate'));

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
baarathi
Contributor II

Re: Increamental load

Thanks a lot. Insert and Update happens perfectly, but the delete in increatemental load in a problem in above script, how to solve it?

MVP
MVP

Re: Increamental load

The problem with delete incremental loads is that you often need to read much more information to determine if something is missing. If the deletes happen in a limited time frame (say up to 30 days ago), then you could use a created date based approach with a cut-off date. In pseudocode, that could look like this

     Let vCutoff = Date(Today() - 30, 'yyyy/MM/dd');

    

     Data:

     SQL SELECT ID,

          CreatedDate,

          ...

     From ...

     Where CreatedDate >= '$(vCutoff)';

     Concatenate

     LOAD ID,

          CreatedDate,

          ...

     FROM  .....qvd (qvd)

     Where CreatedDate < '$(vCutoff)';

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
baarathi
Contributor II

Re: Increamental load

Jonathan, Thanks a lot.

Community Browser