This is the script you need:
LOAD * Inline [
File_number, Reserve_line_number, Ranking, Creation_date, Reserve_amount
100001, 1, 4, 05/01/2015, 15000
100001, 2, 3, 16/01/2015, 14200
100001, 3, 2, 09/02/2015, 12600
100001, 4, 1, 19/03/2015, 9600
LOAD File_number, Max(Reserve_line_number) as MaxLine Resident MyTab Where Creation_date <= '11/02/2015' Group By File_number;
LOAD * Resident MyTab Where Reserve_line_number = MaxLine;
DROP Table MyTab;
let me know
Looking at my own question, I realised I did something wrong. Both answers are assuming I will load ONLY the records that are below a given, fixed reference date.
But of course, I will be loading ALL records, up to today. I need to find a way to limit my records (in a worksheet in designer) to the ones I described in the case.
Maybe I posted this in the wrong topic forum? Or are you willing to show me how to do this in Designer?
Of course you are thanked for this solution, which - no doubt - will be useful for other reserve calculations!
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?