topic Display Employee Status on the basis of precedence of 'Status' values in QlikView

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

ankurakash — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

RDBurmon — Fri, 07 Feb 2020 10:23:58 GMT

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)

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

Brett_Bleess — Thu, 05 Mar 2020 16:33:32 GMT

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