Increamental load

[baarathi]

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?

[ali_hijazi]

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

[jonathandienst · Accepted solution]

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

[baarathi]

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

[jonathandienst]

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

[baarathi]

Jonathan, Thanks a lot.