2 Replies Latest reply: Nov 5, 2015 10:58 AM by Gabriel Grünberg RSS

    Compare and store changes

      I need some help with setting up a way to store historical data.

       

      The datasource changes alot and unfortunatly source system doesnt save some changes.

      Unfortunatly things are even hard-deleted from the source which means I want to also find/mark things in QV that they were deleted.

       

      Today what I store looks basicaly like this:

       

       

      Keyamountpricevalue1value2
      1520BigSomething
      22030HugeGoodbye

       

      This is reloaded every night, which means I can always only see the current state of things.

       

       

       

       

      And I want to be able to store compare it each day and store:

       

      Keyamount
      price
      value1
      value2
      ActiveFrom
      ActiveTo
      Deleted
      ActiveVersion
      1520BigSomething2010-08-102012-10-05

      1520SmallSomethingElse2012-10-06

      1
      22030HugeGoodbye2010-08-10
      1

       

      Anyone got a clue where I should start?

      Do I have to manually compare every column of every table? or is there some kind of quicker compare?

      basically I dont care what has changed, as soon as ANYTHING changes I want a new record for it.

        • Re: Compare and store changes

          Hi,

          Could try using a composite key of all the fields you think will change:

           

              Key&'-'&Amount&'-'&Price&'-'&Value1&'-'&Value2 AS CompKey

           

          You can then do the following series of steps:

           

          1. load in your fresh data to TodaysTable

          2. load in yesterday's data (stored off using a qvd from end of this process the day before) to YesterdaysTable

          3. take from todaystable rows which have not changed using where clause:

           

              WHERE EXISTS (CompKey,YesterdaysCompKey);

           

          4. add on rows that have changed, again using a where clause:

           

              WHERE EXISTS (CompKey,YesterdaysCompKey);

           

          That would only account for those rows that were there before, or have changed slightly. As for new rows, you should be able to bring in those one from TodaysTable that didn't exist on yesterdays:

           

              WHERE NOT EXISTS (Key,YesterdaysKey);

          • Re: Compare and store changes

            Had completely forgotten about this post

             

            But Jonathan did get it right, to do something like this you need to load, reload reload reload the data building up one thing at a time. Creating a "checksum" for each ID helps alot.

             

            I've now had this running without much trouble since around the time of the original post.

             

            It is however a terrible time and resource-waster, and if you HAVE to do this then you should be asking source system to create a audit of some sort instead, but it is doable