How to get other values instead of first sorted values?

[#1]

Hi,

If there are values as below

id          status1           status2    DATE

234        US                  Region   23/01/2013

234        US                  GHY       01/12/2012

234        UK                   YUT       03/11/2012

how to get values instead of first sorted value. I need output as below

id          status1           status2    DATE

234        US                  Region    23/01/2013

234        UK                   YUT       03/11/2012

can anyone suggest how to do this?

Thanks.

[#2]

The required output on internal table in script or UI ?

[giakoum]

firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]])

Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause. Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return NULL. By stating an n larger than 1, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.

Example:

Load Customer,

firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought from abc.csv

group by Customer;

By stating an n larger than 1, the nth value in order will be returned :

That means that if you do not want the first sorted value, you change n to 2 and you get the 2nd sorted value.

or you could simply add a -DATE and the DATE field will be ordered descending.

Depends how your data is

[#4]

yoy would do your load and use max and group, using the max of the date, group by status1 field

If I understand, it looks like when you ahve multiple status1 with the same value, you want the max date of the entries

[#5]

yes. how to do this? please help

[#6]

need in internal table. would it be possible?

[#7]

I have used like below. but it is not showing correct value.

firstsortedvalue (IF(status1='US',DATE(LEFT(Date,10),'DD/MM/YYYY')) , -3 )

actually below are the date values in Date

26/01/2015

20/02/2015

27/10/2014

the correct date should show is 26/01/2015 but it is showing 27/10/2014

how to get correct date. Please help

[avinashelite]

try like this :

Table_A:

load * inline [

id,status1,status2,DATE

234, US,Region,23/01/2013

234,US,GHY,01/12/2012

234,UK,YUT,03/11/2012

];

status2_mapping:

mapping load

id&'-'&status1&'-'&DATE as Key

status2

resident

Table_A;

Temp:

LOAD id as New_ID,

status1 as New_Status1 ,

max(DATE) as New_DATE

group by

id, status1

resident

Table_A;

Drop table Table_A;

Result:

LOAD applymap('status2_mapping',New_ID&'-'&New_Status1&'-'&New_DATE) as  status2,

New_ID as id,

New_Status1 as status1,

New_DATE as Date

resident

Temp;

Drop table Temp;

[#9]

Thanks. instead of doing in script if I want to do in expression how to do this?

firstsortedvalue (IF( status1='US',DATE(LEFT(Date,10),'DD/MM/YYYY')) , -3 )

actually below are the date values in Date

26/01/2015

20/02/2015

27/10/2014

[#10]

Hi,

Try concat the date field like,

ex: 23/01/2013 as 23012013 and use minstring for this value,minstring(23012013) and use Group by same concat field

Hope this works

Regards

Malli