Get value with max transaction date

[dapostolopoylos]

Hello everybody, i will need a little help on that...

Lets assume that i have the following data table:

LOAD * INLINE [

    productId, description, Trans_date, Trans_id

    4098, desc1, 2013-01-01, 1

    4098, desc1, 2013-06-20, 2

    4098, desc2, 2014-01-01, 3

    4098, desc2, 2014-06-02, 4

    4098, desc2, 2014-07-30, 5

    4098, desc1, 2013-07-30, 6

];

I want to create a new field in my load script called for example "final_description" that will have the value of the "description" column where "Trans_date" = Max('Trans_date").

In my example the "final_description" should be equal to desc2 because Max('Trans_date") = 2014-07-30...

Any suggestions?

[#2]

Table:

LOAD * INLINE [

    productId, description, Trans_date, Trans_id

    4098, desc1, 2013-01-01, 1

    4098, desc1, 2013-06-20, 2

    4098, desc2, 2014-01-01, 3

    4098, desc2, 2014-06-02, 4

    4098, desc2, 2014-07-30, 5

    4098, desc1, 2013-07-30, 6

];

TempTable:

Load productId,

description where Trans_date like max(Trans_date) as max_trans_date

Resident Table;

Inner Join (Table)

Load *

Resident TempTable;

Drop table TempTable;

[MayilVahanan · Accepted solution]

Hi

Try like this

Test:

LOAD * INLINE [

    productId, description, Trans_date, Trans_id

    4098, desc1, 2013-01-01, 1

    4098, desc1, 2013-06-20, 2

    4098, desc2, 2014-01-01, 3

    4098, desc2, 2014-06-02, 4

    4098, desc2, 2014-07-30, 5

    4098, desc1, 2013-07-30, 6

];

Load FirstSortedValue(description, -Trans_date) as final_description, productId Resident Test Group by productId;

[Anonymous]

Hi,

Try with this.It is working fine.

LOAD * INLINE [
    productId, description, Trans_date, Trans_id
    4098, desc1, 2013-01-01, 1
    4098, desc1, 2013-06-20, 2
    4098, desc2, 2014-01-01, 3
    4098, desc2, 2014-06-02, 4
    4098, desc2, 2014-07-30, 5
    4098, desc1, 2013-07-30, 6
];

Temp:
Load
max(Trans_date) as max_Trans_date
Resident Table;

Let vMaxTransDate=peek('max_Trans_date');

inner join(Table)
Load productId,
description  as finalDesc
Resident Table
where Trans_date = $(vMaxTransDate) ;

drop table Temp;

[dapostolopoylos]

Thanks everyone for their help!

The implementation i did is the following:

allTxn:

LOAD * INLINE [

    productId, description, Trans_date, Trans_id

    4098, desc1, 2013-01-01, 1

    4098, desc1, 2013-06-20, 2

    4098, desc2, 2014-01-01, 3

    4098, desc2, 2014-06-02, 4

    4098, desc2, 2014-07-30, 5

    4098, desc1, 2013-07-30, 6

    4091, desc3, 2012-05-06, 7

    4091, desc4, 2014-05-27, 8

    4091, desc3, 2012-05-16, 9

];

inner join

Load

productId,

firstsortedvalue(description, -Trans_date) as final_description

Resident allTxn

Group by productId;

Be well...