topic Re: How Delete work's in Insert, Update & Delete method of Incremental Load? in App Development

How Delete work's in Insert, Update & Delete method of Incremental Load?

SanjeevR — Wed, 21 Jun 2023 12:50:01 GMT

/////////// Exposure ///////////
temp_MaxDate:
LOAD Timestamp((max(datetime_modified))) as MaxDate
FROM $(vPath)\vw_ext_exposures_$(vDBName2).qvd(qvd);

let vMaxDateE = Peek('MaxDate',0,'temp_MaxDate');

drop Table temp_MaxDate;

Exposure:
sql SELECT *
FROM $(vDBName1).vw_ext_exposures
WHERE datetime_modified <= '$(vMaxDateE)';

let vRowsIncE2R2 = NoOfRows('Exposure');
trace $(vRowsIncE2R2);

Concatenate
LOAD *
FROM $(vPath)\vw_ext_exposures_$(vDBName2).qvd(qvd)
WHERE NOT EXISTS(exposure_id);

let vRowsIncE2R3 = NoOfRows('Exposure');
trace $(vRowsIncE2R3);

Inner Join
Load exposure_id
from
$(vPath)\vw_ext_exposures_$(vDBName2).qvd(qvd);

let vRowsIncE2R4 = NoOfRows('Exposure');
trace $(vRowsIncE2R4);

STORE Exposure into $(vPath)\vw_ext_exposures_$(vDBName2).qvd(qvd);

Drop Table Exposure;

EXIT SCRIPT;

Re: How Delete work's in Insert, Update & Delete method of Incremental Load?

rwunderlich — Wed, 21 Jun 2023 15:29:01 GMT

I think your Inner Join is incorrect. Your source for exposure_id should be the DB table, not the QVD. Like this:

Inner Join (Exposure)
SQL Select exposure_id
FROM $(vDBName1).vw_ext_exposures;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: How Delete work's in Insert, Update & Delete method of Incremental Load?

SanjeevR — Wed, 21 Jun 2023 16:26:23 GMT

Hi Rob,

Thanks for the solution, I have tried it however I was trying not to hit the Database again as I already load where the datetime_modified is >= to the max date because it drastically increases the reload time. I was hoping to utilise the last updated QVD that I saved away from the previous day for the inner join because of the time constraint but it looks like that my approach will not work.

Do you know of another approach that I can try to attain the same result?

Regards,

Sanjeev

Re: How Delete work's in Insert, Update & Delete method of Incremental Load?

rwunderlich — Wed, 21 Jun 2023 16:50:19 GMT

I do not know of any other approach. You must match the keys between the DB and the QVD to detect deletes. Unless your DB keeps a record of deletes in another table.

Note that for the Inner Join Select, you are only selecting one field, the key field. This should be an index field and a lightweight operation to the DB.

-Rob