How to show last update for each record...?

[Dayna]

Dear all,

I hope that you can help me, I'm trying to show in a chart the last entry (i.e. last update) for each record..

i.e. ID1 has 6 updates, I want to show the last. ID2 has 1 update, show the last (and first), ID3 has 4 updates (show the last). But I need to show this for each record, not the last overall.

I hope that you can help me!!

Kind Regards,
Dayna

[#2]

Hi Dyna ,

Sorry , I have not noticed the user group...i.e Request Id is occupied by more than one user ...

The following code will rectify that..

LOAD

Max

(Time(SUN_IH_Update)) as Time

,

Max

(Date(SUN_IH_Update)) as SUN_IH_Update

,

SUN_ServiceReqID

,

SUN_IH_AddedBy

from

[Sample Data.xlsx]

(

ooxml, embedded labels, table is Sheet1) group by SUN_ServiceReqID,

SUN_IH_AddedBy;

Regards,

Chakravarthy.

[#3]

Hope this will help..........

Table:
LOAD //SUN_IH_AddedBy,
 max(date(SUN_IH_Update,'DD-MM-YYYY HH:mm:ss')) as SUN_IH_Update,
 SUN_ServiceReqID
FROM

(ooxml, embedded labels, table is Sheet1)
group by SUN_ServiceReqID;

inner join

LOAD SUN_IH_AddedBy,
 SUN_ServiceReqID,
 date(SUN_IH_Update,'DD-MM-YYYY HH:mm:ss') as SUN_IH_Update
FROM

(ooxml, embedded labels, table is Sheet1) ;

Regards

[Dayna]

Hello All,

I think my problem may be more complex than I first thought.

I need the last update of a record, for any incident that has the status of 'open'. (The last update of the incident, not each user...)

I also need to ignore certain operations, including 'Add Incident', 'Open Incident', 'Resolve Incident', Assign Incident', Close Incident', Assign To Me and possibly others...

I've included an updated set of data!

Hope you can help me with my problems....
Kind Regards,
Dayna

[Dayna]

I tried to do something like this in a straight table, but no success...:

firstsortedvalue(DISTINCT aggr(max({$<SUN_State = {'Open'}>} SUN_IH_Update),SUN_IH_Update,SUN_IH_AddedBy,SUN_IH_IncidentID,SUN_IH_Operation),-SUN_IH_Update)

Don't know if it's the way forward or not!

Kind Regards,
Dayna

[#6 · Accepted solution]

Hi Dayna,

see the attached example.

Maybe it gives an idea how you can get what you want.

Good luck!

Rainer

[Dayna]

I think that'll do it!! Thank you very much Rainer, and thanks to everyone who helped!

Kind Regards,
Dayna