7 Replies Latest reply: Aug 20, 2015 1:45 AM by Hans de Vries RSS

    Search records by reference date

    Hans de Vries

      I'm working in the database of an insurance company, using QlikView. Each time a payment is done in a damage, the initial reserve is lowered by that amount, and a new line is added in the reserve data table. That line is numbered (simply, starting by 1) and the "ranking" field is modified in the previous records. The amount of reserve in the line with the lowest ranking, is the status of the current reserve for that damage. Here is a simplified example, showing 4 records in the reserve database:

       

      File_numberReserve_line_numberRankingCreation_dateReserve_amount
      1000011405/01/201515000
      1000012316/01/201514200
      1000013209/02/201512600
      1000014119/03/20159600

       

       

      Now, what I would like to do, is make a view of the total reserve for a given reference date. This date does NOT need to be present in the Reserve_amount field! So let's assume the reference date is 11/02/2015 (dd/mm/yyyy). How do I include totalize all records in my table that are before or ON that reference date? And remember, for each file_number, I need only ONE record, and ONE amount, in my example that woud lead to reserve_line_number = 3, Ranking = 2, Creation_date = 09/02/2015, and Reserve_amount (for that file only) = € 12,600. It is only this line I need to totalize for all File_numbers.. I've really been busting my brain over this...

      Excuse my poor English; it's not my native language.. I hope I made my case clear....