Incremental load, no modification flag

[#1]

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 ?

[Gysbert_Wassenaar]

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.

[marcus_malinow]

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.