Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

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
Highlighted

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

View solution in original post

5 Replies
Highlighted
Partner
Partner

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'));

I can walk on water when it freezes
Highlighted

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

View solution in original post

Highlighted
Creator III
Creator III

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

Highlighted

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
Highlighted
Creator III
Creator III

Jonathan, Thanks a lot.