Perform Incremental Load On Qlik Sense

Question

New to Qlik Sense.

I would like to perform incremental insert, update and delete. Through research i managed to write this script

//This fetches deleted records
SELECT `sale_detail_auto_id` 
FROM `iprocure_ods.deleted_records` as dr
INNER JOIN `iprocure_ods.saledetail` sd ON sd.sale_detail_auto_id = dr.identifier AND dr.type = 2 
WHERE dr.delete_date > TIMESTAMP('$(vSaleTransactionsRunTime)');

//This fetches new and updated records
[sale_transactions]:
SELECT *
FROM `iprocure_edw.sale_transactions`
WHERE `server_update_date` > TIMESTAMP('$(vSaleTransactionsRunTime)');

Concatenate([sale_transactions])
LOAD *
FROM [lib://qlikPath/saletransactions.qvd] (qvd) Where Not Exists(`sale_detail_auto_id`);

//This part updates runtime dates
MaxUpdateDate:
LOAD Timestamp(MAX(`server_update_date`), '$(TimestampFormat)') As maxServerUpdateDate
FROM [lib://qlikPath/saletransactions.qvd] (qvd);
Let vSaleTransactionsRunTime = peek('maxServerUpdateDate', 0, MaxUpdateDate);
DROP Table MaxUpdateDate;

New and update records works fine. The problem is with the deleted records are replaced with empty column except sale_detail_auto_id column.

How can i fetch data from saletransactions.qvd that are not in deleted records?

Answer 1

In first SELECT you select sale_detail_auto_id fields which is also exists under the same field name in new and updated records, so then you see deleted ids together with new ones. You need to rename that column to avoid conflict.

Please use AS, for example:

sale_detail_auto_id` AS `deleted_sale_detail_auto_id`

and then in EXISTS use that field:

Where Not Exists(deleted_sale_detail_auto_id, sale_detail_auto_id);

UPDATED:

Additionally I think it doesn't make sense to store deleted ids in data model so you can name that table:

[TEMP_deleted_ids]
SELECT sale_detail_auto_id` AS `deleted_sale_detail_auto_id`

and then in the end of the script remove it:

DROP Table [TEMP_deleted_ids];