topic Re: Incremental load, no modification flag in QlikView

Incremental load, no modification flag

Mon, 16 Sep 2013 12:28:24 GMT

I have a table looking roughly like below which I read into QVD every morning. My problem is that sometimes the field AMOUNT is changed and I have no modification flag that show it. How can I do a insert, update, delete load ?

Before change:

Case_id AMOUNT Date Function_code UserID RowID
a 523,33 01-06-2012 DB 55 4

After change:

Case_id Amount Date Function_code UserID RowID

a 215,60 01-06-2012 DB 55 4

I only want to load and keep the latest value. Any ideas ?

Re: Incremental load, no modification flag

Gysbert_Wassenaar — Mon, 16 Sep 2013 13:11:07 GMT

Since there is no way to determine what actually is the latest value what you want is not possible. You really will need a field that indicates that a record has changed.

Re: Incremental load, no modification flag

marcus_malinow — Mon, 16 Sep 2013 13:18:10 GMT

ok, I think this may be possible, however it's a fairly horrible solution.

1 - load from your qvd, and include an additional field, a concatenation of Case_id and Amount.

2 - use a concat to concatenate a comma separated list of all possible values of the new concatenated field

3 - using the concat string from step 2, query your database for any records where the concatenation of Case_id and Amount isn't in your list.

4 - Concatenate on to this table from your existing qvd 'where not exists(RowID)'

As I said, it's horrible.

A better solution would be to add a new modified date field to your source table, and set up a trigger to populate it on insert or update.