topic Re: Search records by reference date in QlikView

Search records by reference date

hansdevr — Mon, 17 Aug 2015 06:13:30 GMT

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_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

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....

Re: Search records by reference date

alexandros17 — Mon, 17 Aug 2015 09:27:02 GMT

This is the script you need:

MyTab:
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
];

Left Join
LOAD File_number, Max(Reserve_line_number) as MaxLine Resident MyTab Where Creation_date <= '11/02/2015' Group By File_number;

FinalTab:
NoConcatenate
LOAD * Resident MyTab Where Reserve_line_number = MaxLine;
DROP Table MyTab;

let me know

Re: Search records by reference date

jonathandienst — Mon, 17 Aug 2015 09:34:59 GMT

HIC makes some suggestions on how to handle this scenario here: How to populate a sparsely populated field

Re: Search records by reference date

hansdevr — Mon, 17 Aug 2015 10:36:34 GMT

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!

Re: Search records by reference date

hansdevr — Mon, 17 Aug 2015 10:38:03 GMT

Again, thanks for your hint, which indeed proved useful (in a load script)!

Please see answer to previous poster. I'm afraid I'm looking for a way to achieve this goal in designer, using a standard worksheet object...

Re: Search records by reference date

hansdevr — Mon, 17 Aug 2015 10:44:41 GMT

BTW, I couldn't get your script to work...

Hint: I'm a total newbie on QlikView...

Re: Search records by reference date

hansdevr — Mon, 17 Aug 2015 12:12:12 GMT

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!

Re: Search records by reference date

hansdevr — Thu, 20 Aug 2015 05:45:08 GMT

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?