topic Re: Incremental load to remove deleted records in QlikView

Incremental load to remove deleted records

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

I am pulling in my orders from a SQL database up to the end of 2016, then using the incremental load to pull in 2017. The problem i am having is if a order is deleted from the database in 2017, it remains in the QVD file. is it possible to remove 2017 records from the QVD file and reload fresh data.

Thanks

Chris

Re: Incremental load to remove deleted records

hector_munoz — Wed, 08 Mar 2017 21:21:49 GMT

Hi Chris,

In this thread you have some valious information about incremental load:Incremental load in Qlikview

Regards,
H

Re: Incremental load to remove deleted records

rwunderlich — Wed, 08 Mar 2017 22:37:21 GMT

You can delete rows from the QVD by reconcilling using and INNER JOIN.

Orders:

LOAD new incremental rows....

// Merge rows from QVD

Concatenate (Orders)

LOAD * FROM Orders.qvd (qvd)

WHERE NOT Exists(OrderId);

// Process deletes

INNER JOIN (Orders)

SQL SELECT OrderId FROM Sales.Orders;

STORE Orders INTO Orders.qvd;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Incremental load to remove deleted records

ccsimo16 — Thu, 09 Mar 2017 07:56:17 GMT

Thanks you very much, this has worked for me.

Re: Incremental load to remove deleted records

Anonymous — Thu, 20 Apr 2017 14:50:52 GMT

Hi ROB,

Please give the answer of the incremental load scenario:

Yesterday we have loaded the data that contains 100 rows and today user has deleted 5 records from source database and your incremental load run so no inserted record will found because their n new record is inserted and updated but 5 record is deleted from source data. How incremental load is identify the those 5 deleted record and present in your QVD.

I need t know today which record is deleted.

Re: Incremental load to remove deleted records

rwunderlich — Fri, 21 Apr 2017 17:01:16 GMT

To identify the records that have been deleted.

1. Load all the keys from the source database in a new field, for example "Key2".

2. Load the QVD Keys using

where not exists(Key2, Key)

to load a table of the deleted keys.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com