Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hansdevr
Contributor II

Search records by reference date

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

7 Replies

Re: Search records by reference date

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

MVP
MVP

Re: Search records by reference date

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
hansdevr
Contributor II

Re: Search records by reference date

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!

hansdevr
Contributor II

Re: Search records by reference date

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

hansdevr
Contributor II

Re: Search records by reference date

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

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

hansdevr
Contributor II

Re: Search records by reference date

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!

hansdevr
Contributor II

Re: Search records by reference date

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?

Community Browser