Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
SanjeevR
Contributor
Contributor

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

/////////// 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;

Labels (1)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

SanjeevR
Contributor
Contributor
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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