Calculate final status based on max date among multiple rows

eytezc — Sat, 16 Nov 2024 19:35:41 GMT

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

Re: Calculate final status based on max date among multiple rows

eytezc — Mon, 25 Nov 2019 10:55:40 GMT

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];