Data Issue

[#1]

I have data like below.

LeaseID  SubLeaseID    ExpDate

123          456                  5/9/2016

123          678                  5/9/2017

234          890                  6/9/2016

Here when I have Multiple subleaseID's for one LeaseID I only want to pull the ID with the max expdate and I want to do it in the script level as this will be linked to another table with expenses.

I want the output to look like below.

LeaseID  SubLeaseID    ExpDate

123          678                  5/9/2017

234          890                  6/9/2016

How can I do this in the backend script ?

Thanks much.

[sunny_talwar]

There are multiple ways you can do this. One of them would be this:

Method 1:

Table:

LOAD * Inline [

LeaseID, SubLeaseID, ExpDate

123, 456, 5/9/2016

123, 678, 5/9/2017

234, 890, 6/9/2016

];

Right Join(Table)

LOAD LeaseID,

  Max(ExpDate) as ExpDate

Resident Table

Group By LeaseID;

[sunny_talwar]

Or this

Method 2:

Table:

LOAD LeaseID,

  FirstSortedValue(SubLeaseID, -ExpDate) as SubLeaseID,

  Date(Max(ExpDate)) as ExpDate

Group By LeaseID;

LOAD * Inline [

LeaseID, SubLeaseID, ExpDate

123, 456, 5/9/2016

123, 678, 5/9/2017

234, 890, 6/9/2016

];

Method 3:

Table:

LOAD * Inline [

LeaseID, SubLeaseID, ExpDate

123, 456, 5/9/2016

123, 678, 5/9/2017

234, 890, 6/9/2016

];

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where LeaseID <> Previous(LeaseID)

Order By LeaseID, ExpDate desc;

DROP Table Table;

[#4]

Thank you Sunny.I appreciate your help

I am sorry but now the request is pick the max amount.

My Data looks as below.

Lease:

LeaseID  SubLeaseID    ExpDate

123          456                     5/9/2016

123          678                     5/9/2017

234          890                     6/9/2016

ExpTable:

SubleasID      Exp

456                   56,897

678                   43,890

890                   43,210

They want the o/p as below

LeaseID  SubLeaseID    ExpDate          Exp

123          456                     5/9/2016          56,897

234          890                     6/9/2016           43,210

Can this be done ?  How can i pick the max amount for each subspace ID. Thank you very much.

[sunny_talwar]

Try this:

Table:

LOAD * Inline [

LeaseID, SubLeaseID, ExpDate

123, 456, 5/9/2016

123, 678, 5/9/2017

234, 890, 6/9/2016

];

MappingExpTable:

Mapping

LOAD * Inline [

SubleasID,  Exp

456,        56897

678,        43890

890,        43210

];

FinalTable:

LOAD LeaseID,

  FirstSortedValue(SubLeaseID, -Exp) as SubLeaseID,

  FirstSortedValue(ExpDate, -Exp) as ExpDate,

  Max(Exp) as Exp

Group By LeaseID;

LOAD *,

  ApplyMap('MappingExpTable', SubLeaseID) as Exp

Resident Table;

DROP Table Table;

UPDATE:
You can also use Joins instead of ApplyMap if there are more fields in your ExpTable table. I did ApplyMap assuming there are only two fields, but in your case it might make sense to just to a left join.