2 Replies Latest reply: Sep 16, 2013 9:18 AM by Marcus Malinow RSS

    Incremental load, no modification flag

    Annette Søgaard

      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

          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

            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.