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:
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....
Jonathan, after having had a second glance at your suggestion, I must say this might work indeed. It's a matter of populating a table with missing dates... I will try this within our own situation and keep you updated!
I'm afraid that I couldn't get this trick to work properly. In my table there are many fields, one of them is the nr. of the customer file. For each customer file, this trick of adding empty records (by date field) should be repeated, which does not happen. Most probably I am doing something wrong, but what?