Display Employee Status on the basis of precedence of 'Status' values

[ankurakash]

Hi Qlikers

Request your help with below QlikView scenario

Sample Data:  (sample data and expected outcome is shared in the attached excel file)

Course_Id  Course_Name  Emp_Id  Emp_Name  Status             Status_Id        TimeStamp

C1                Qlik                        E1              Kevin               In Progress      2                  07-Feb-2020 15:18:03

C1               Qlik                        E1              Kevin                Completed      3                  07-Feb-2020 14:20:02

 We have to retain employee training activity records in data model and need to display a tabular chart(straight/ pivot table) where we are showing Course_Name, Emp_Name and their Status.

The condition is, if the employee has completed the course and is reattempting the same course, we need to display the outcome as completed for the employee.

'Status' field precedence would be: Completed > In Progress > NA

I was working with the approach of using FirstSortedValue on Status_Id and grouping the data on Course_Name but it did not worked out.

Request your support and guidance for the approach I shall take to achieve the outcome. The sample file has the sample data and expected outcome table.

Thank you.

[RDBurmon · Accepted solution]

Here you go - tbl: LOAD Course_Id & '_' & Emp_Id & '_' & StatusId as Key, * Inline [ Course_Id,Course_Name,Emp_Id,Emp_Name,Status,StatusId,Timestamp C1,Python,E1,Kevin,In Progress,2,2/7/2020 16:07 C1,Python,E1,Kevin,Completed,3,2/7/2020 14:07 C1,Python,E2,Gau,Completed,3,2/4/2020 13:07 C1,Python,E3,Sat,NA,1,2/10/2020 14:18 C3,Qlik,E1,Kevin,Completed,3,2/14/2020 2:07 C3,Qlik,E2,Gau,In Progress,2,2/11/2020 11:07 C3,Qlik,E2,Gau,Completed,3,2/11/2020 10:07 C3,Qlik,E3,Sat,Completed,3,2/17/2020 2:07 ]; NoConcatenate tbl1: LOAD Course_Id & '_' & Emp_Id & '_' & Max(StatusId) as Key, 'FinalStatus' as ISFinalStatus Resident tbl group by Course_Id,Emp_Id

and then add straight table with below expression. Hide this expression 

=Count({<ISFinalStatus={'FinalStatus'}>}Status)

[Brett_Bleess]

Did the reply you received get you what you needed for your use case?  If so, do not forget to return to your thread to close things out by using the Accept as Solution on the poster's reply to give them credit for the help and let other Members know that worked for you.  If you are still working on things, please leave an update, and if you did something different, please consider posting what you did and then mark that as the solution...

Regards,
Brett