Insert, Update, Delete Incremental Load Without Entire History

Tue, 11 Nov 2014 17:22:00 GMT

So basically we get a nightly feed which includes 7 days worth of updated/new data. Along with that we get a file including all deleted id's. My objective is to do an incremental load, insert and update, using my initial QVD (made form a "catch up" file) and the 7 day file we get nightly. I then need to use the deleted ID file to remove any records from the table I created in the incremental load. Since we don't get a full export I can't utilize the inner load of all IDs most people use.

What would be the best way to go about this?

Re: Insert, Update, Delete Incremental Load Without Entire History

Gysbert_Wassenaar — Wed, 12 Nov 2014 12:42:32 GMT

Maybe like this:

// load deleted ID's in a temporary table
T1:
LOAD ID FROM deleted_ids_file (txt, ...etc...) ;

// load only the records from the incremental_data qvd that
// are not in the deleted id's file in a temporary table
T2:
LOAD * FROM incremental_data.qvd (qvd)
WHERE NOT EXISTS(ID);

// store the records from the remaining ID's
STORE T2 into incremental_data.qvd (qvd);

// drop tables that are no longer needed
DROP TABLES T1, T2;

// load the updated and new records from the nightly 7 day file
Final:
LOAD * FROM sevendaysupdates.qvd (qvd);

// load only the records from the incremental_data qvd that are
// not already in the nightly 7 day file
concatenate(Final)
LOAD * FROM incremental_data.qvd (qvd)
WHERE NOT EXISTS(ID);

// store the complete set of records in the incremental qvd
STORE T2 into incremental_data.qvd (qvd);

topic Insert, Update, Delete Incremental Load Without Entire History in QlikView

Insert, Update, Delete Incremental Load Without Entire History

Re: Insert, Update, Delete Incremental Load Without Entire History