topic Re: Load most recent record and right join in QlikView

Load most recent record and right join

Thu, 08 Oct 2015 03:23:24 GMT

Here is the table

Loan_Number amt my_date

111111 500 11/12/2014 *

111111 750 1015/2014

11112 1221 09/10/2015

11112 652 10/02/2014 *

The table name is loan_records

I need to load loan_records in descending order by loan_Number and my_date and only capture the latest transaction, in this case the two marked with *

I then need to right join it to an existing table named inventory. That table looks like this

Inv_No Loan_Number

11 111111

12 111112

How can I load and join the two tables while only grabbing the most recent record in loan_records?

Re: Load most recent record and right join

saurabhwadhwa — Thu, 08 Oct 2015 03:40:40 GMT

Hi ,

I think you can achieve this using firstsortedvalue() function .

Thanks

Re: Load most recent record and right join

sunny_talwar — Thu, 08 Oct 2015 03:50:38 GMT

May be this:

MainTable:

LOAD * Inline [

Inv_No, Loan_Number

11, 111111

12, 111112

];

Table:

LOAD * Inline [

Loan_Number, amt, my_date

111111, 500, 11/12/2014

111111, 750, 10/15/2014

111112, 1221, 09/10/2014

111112, 652, 10/02/2014

];

Right Join (MainTable)

LOAD Loan_Number,

Max(my_date) as my_date,

FirstSortedValue(amt, -my_date) as amt

Resident Table

Group By Loan_Number;

DROP Table Table;

Output: