Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eytezc
Contributor II
Contributor II

Calculate final status based on max date among multiple rows

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

Labels (5)
1 Solution

Accepted Solutions
eytezc
Contributor II
Contributor II
Author

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

View solution in original post

1 Reply
eytezc
Contributor II
Contributor II
Author

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