1 Reply Latest reply: May 23, 2012 4:14 PM by Calvin Francart RSS

    Dealing with duplicates

    Calvin Francart

      I am modifying an existing analyzer where the data is being saved on a daily basis to a qvd file, and also being updated incrementally using a csv export from our ticketing system. Several of the fields can now change from what the stored value might have been, while others may remain unchanged; what's the recommended method for changing values when this happens?

       

      For example, in the stored .qvd I have these values:

       

      Ticket Number         Last Edit Date       Last Edited By

      2090                       5/5/2012               Jane Doe

      2091                       5/5/2012               John Doe

       

      The update file that I am shipped contains data where the last edit date is within the past 30 days (this is a limitation of the ticketing system report and completely out of my control).

       

      When the update runs, I get something like this:

       

      Ticket Number         Last Edit Date       Last Edited By

      2090                       5/5/2012               Jane Doe

      2091                       5/21/2012             Jane Doe

       

      (This is a status dashboard, so I need to report only on the last edit date.)

       

      I've tried loading the fields that can change into a single temporary table, by first loading from the .qvd and then concatenating from the update file, then using FirstSortedValue in the load script to join the latest edit date value to the ticket number. It seems to work, but only if the data changes; if the Last Edit Date is the same in both the .qvd and the update file, the tickets that have not changed actually drop out of the displayed details. I am guessing that this is because there is no 'latest' version of the as I am using the '-' option.

       

      Does anyone have any suggestions on how to make this work?