Load most recent record and right join

[#1]

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?

[saurabhwadhwa]

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: