Qlik Community

Ask a Question

App Development

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

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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