Hello Qlikers,
I have 2 questions in one.
I have 4 Tables named:
1- Employee table
2- First Review
3- Second Review
4- Third Review
1- the employee Table which has the (Person_ID,Emp_num,Name,Start_Date,End_Date) (start and end date of the employee in a certain department, and if the employee end date is < from today date then he is not active anymore and if the end date has "31/12/4102" then he is still active.
2- the First Review ( Person_ID,Emp_num,Name,Interview_Date,Status,Manual_flag ='First_Interview') ** this interview only have 1 record per employee per Date.
3- the Second Review ( Person_ID,Emp_num,Name,Interview_Date,Status,Manual_flag ='Second_Interview') ** An employee can have multiple second's review per Date
4- third review ( Person_ID,Emp_num,Name,Interview_Date,Status,Manual_flag ='Third_Interview') **this interview only have 1 record per employee per Date.
what i have done is the following:
1- I concatenated the 3 interviews with the manual_flag diffrentiate between them.
2- I have created a script that gives me the years between the (start_date,end_date) of the employee Named (employee_year), and i have created a composite_key that is from (employee_year and person_ID)
3- and a composite key for the concatenated tables (Let's name it "Review") using the (Year of the review date and the person_ID)
3- I associated the Review Table with the employee Table using the composite_key
I have 2 questions here:
1- using the assossication , how can i know if an employee doesn't have a review? and if so i need to put them in the same table while they are flagged with red in the name of the employee and a kpi with their count.
2- if i selected a review year , let us say its 2019 , but the employee was in 2 departments in 2019, if we said that an employee had first review in the first of 2019 but at the end of 2019 he was in a second department and he did not have a first review, this will reflect on the KPI of the old department, but the employee was already transferred from it. ( i am having this problem becuase of the compsite_key is defined from the YEAR of 2019 not the FULL DATE).
This is a demo of the data:
| Comp_key |
person_ID |
emp_num |
name |
StartDate |
EndDate |
EmploymentYear |
Department |
Is the emp Active? |
Is this active depatment |
| 102019 |
10 |
1 |
A |
05/09/2019 |
06/10/2020 |
2019 |
HR |
Y |
N |
| 102020 |
10 |
1 |
A |
05/09/2019 |
06/10/2020 |
2020 |
HR |
Y |
N |
| 102020 |
10 |
1 |
A |
07/10/2020 |
31/12/4102 |
2020 |
Training |
Y |
Y |
| 102021 |
10 |
1 |
A |
07/10/2020 |
31/12/4102 |
2021 |
Training |
Y |
Y |
| 102022 |
10 |
1 |
A |
07/10/2020 |
31/12/4102 |
2022 |
Training |
Y |
Y |
| 202020 |
20 |
2 |
B |
07/08/2020 |
05/09/2021 |
2020 |
HR |
N |
N |
| 202021 |
20 |
2 |
B |
07/08/2020 |
05/09/2021 |
2021 |
Training |
N |
N |
| 202021 |
20 |
2 |
B |
06/09/2021 |
31/12/2021 |
2021 |
Procurement |
N |
Y |
Q.How can i create the flags columns (is this employee active?, is this active department)?
Review Table:
| Comp_key |
person_ID |
emp_num |
name |
Review Date |
Review Year |
Review Flag |
Status |
| 102019 |
10 |
1 |
A |
10/10/2019 |
2019 |
First_review |
Completed |
| 102019 |
10 |
1 |
A |
05/11/2019 |
2019 |
Second_review |
Completed |
| 102019 |
10 |
1 |
A |
05/12/2019 |
2019 |
Second_review |
Completed |
| 102020 |
10 |
1 |
A |
10/10/2020 |
2020 |
First_review |
Completed |
| 102020 |
10 |
1 |
A |
07/11/2020 |
2020 |
Third_review |
Completed |
| 202020 |
20 |
2 |
B |
07/08/2020 |
2020 |
First_review |
Completed |
| 202020 |
20 |
2 |
B |
05/10/2020 |
2020 |
Second_review |
Completed |
| 202020 |
20 |
2 |
B |
06/10/2020 |
2020 |
Third_review |
Completed |
| 202021 |
20 |
2 |
B |
10/08/2021 |
2021 |
First_review |
Completed |
| 202021 |
20 |
2 |
B |
10/10/2021 |
2021 |
Third_review |
Completed |
As we can see in the table,
Employee A has First review and 2 seconds review in 2020 But the third review is missing, and he is an active employee.
Employee B has all 3 reviews in 2020 but only has the first and the third_review in 2021 and the second review is missing.
How may i display that A has a missing third review in 2020 and B has a second review missing in 2021.
Am sorry for the long post but this is the issue am currently dealing with. and need help with it.
Thank you so much.
