How to findout latest records with their status

[#1]

Dear Experties,

How to find out latest record with latest status.please see the below example table :

ID	StartDate	Days	StatusReason
A1001	01/01/2017 10:08:11	10	closed
A1001	01/01/2017 11:08:11	15	Pending	latest record
A1001	31/01/2017 10:08:11	30	closed
A1002	01/02/2017 12:10:13	12	Pending
A1002	21/02/2017 12:10:13	15	closed	Latest Record

i would like to expecting like below

ID	StartDate	Days	StatusReason
A1001	01/01/2017 11:08:11	15	Pending
A1002	21/02/2017 12:10:13	15	-

It would be helpfull if anyone can provide  the logic.

Regards,

Sunny

[swuehl]

For A1001, shouldn't the record with 31/12/2017 be picked?

In general, you can create a table chart with dimension ID and use FirstSortedValue in your expression:

=FirstSortedValue( StatusReason, -StartDate)

This requires that StartDate shows a numeric representation, i.e. the timestamp should be correctly interpreted as such.

Set the TimestampFormat to your format to support a correct interpretation in your script.

[#3]

Hi Stefan ,

Thank you for the reply,it was typo .it is  31/01/2017  10:08:11.

[#4]

Hi,

I  need to know all the column information same like below not only for ID and status reason

ID	StartDate	Days	StatusReason
A1001	01/01/2017 11:08:11	15	Pending
A1002	21/02/2017 12:10:13	15	-

[swuehl]

You can find the StartDate with

=MaxString(StartDate)

in a chart with dimension ID, and respective Days and StatusReason can be retrieved using FirstSortedValue() as indicated above.

[#6]

Hi ,

I am not able to get exactly output result .the below results i am getting .

[swuehl]

Seems like you are using additional dimensions, Days and StatusReason. Only use the dimension you want your results to group by, ID as I understood.

[vkish16161]

Stefan,

If this has to be done at the script level, could we not use ApplyMap (This has limitation for 2 fields)

But is it possible?

Thanks!

[swuehl]

The straight forward analogon to the suggested chart expressions would be a GROUP BY clause with the same functions:

LOAD

     ID,

     Maxstring(StartDate) as StartDate,

     FirstSortedValue(StatusReason, -StartDate) as StatusReason

FROM ...

GROUP BY ID;

Sunni, if your data shows more than one record with the same max StartDate, the FirstSortedValue() either needs the DISTINCT qualifier or you need to add a small value to the sort weight for tie breaking.

[#10]

Hi Stefan,

I am still facing issue with days and status Reason,it would be helpful if you can  provide a solution  with attached sample data.