Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
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];

View solution in original post