Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load most recent record and right join

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?

2 Replies
saurabhwadhwa
Partner - Contributor III
Partner - Contributor III

Hi ,

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

Thanks

sunny_talwar

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:

Capture.PNG