Calculate final status based on max date among multiple rows

[eytezc]

Hello,

Below you can find an example data set, based on this table I would like to calculate 2 new fields.

Final Stage :  For every Process ID, it is the final stage at the Max(ActionDate)

Time : For every Process ID, it is  Max(ActionDate)-Min(ActionDate)

Process ID     -     Stage    -    Action Date      

1                    Stage 1                    25 Nov. 14:00

1                    Stage 2                    25 Nov. 16:00

1                    Stage 3                    25 Nov. 18:00

2                    Stage 1                    25 Nov. 08:00

2                    Stage 2                    25 Nov. 20:00

Looking forward for your help.

Best Regards

[eytezc · Accepted solution]

Hello guys,

I solved it myself with adding below lines to the load editor. 

LOAD [AbsenceRequestID],

FirstSortedValue( distinct [ApprovalActionEmp], -[ApprovalDate],1) as MyFinalStatus Resident AbsenceRequest_Main Group By [AbsenceRequestID];

LOAD [AbsenceRequestID],

FirstSortedValue( distinct [ApprovalDate], -[ApprovalDate],1) as MyMaxDate Resident AbsenceRequest_Main Group By [AbsenceRequestID];

LOAD [AbsenceRequestID],

FirstSortedValue( distinct [ApprovalDate], [ApprovalDate],1) as MyMinDate Resident AbsenceRequest_Main Group By [AbsenceRequestID];