| ID | Team | ReviewedName | ReviewYear | ReviewFlag |
|---|
| 1002 | Team A | Team A | 2016 | 1 |
| 1035 | Team A | Employee A1 | 2015 | 1 |
| 1005 | Team B | Team B | 2015 | 1 |
| 1563 | Team C | Employee C6 | 2014 | 1 |
| 1058 | Team D | Team D | 2016 | 1 |
| 1123 | Team E | Employee E5 | 2015 | 1 |
| 1568 | Team F | Team F | 2014 | 1 |
| 1025 | Team F | Employee F2 | 2016 | 1 |
This is a table I created in QlikSense from simply filter out, from the source table, all ID that hasn't been reviewed.
Background:
1) All Team and Employee have their own unique ID. I also use ID as the dimension.
2) ReviewedName includes either the Team or Employee who were reviewed
3) Employee A# is part of Team A, Employee B# is part of Team B, etc. as shown by the Team column
4) ReviewedFlag = 1 means Team/Employee were reviewed, 0 means not yet. I use this as a filter within the ID column, ie. Dimension formula is: if(ReviewedFlag = '1', ID, null())
5) The rest of the column are just fields
Goal:
I want to further reduce the table by the following rule: If both the Team and any Employee in that team were reviewed previously, only show the one with the most recent ReviewYear. In other words, I want the table to look like this, with the row in red row deleted. It also means, there will be no duplicate of values in the Team Column.
| ID | Team | ReviewedName | ReviewYear | ReviewFlag |
|---|
| 1002 | Team A | Team A | 2016 | 1 |
| 1035 | Team A | Employee A1 | 2015 | 1 |
| 1005 | Team B | Team B | 2015 | 1 |
| 1563 | Team C | Employee C6 | 2014 | 1 |
| 1058 | Team D | Team D | 2016 | 1 |
| 1123 | Team E | Employee E5 | 2015 | 1 |
| 1568 | Team F | Team F | 2014 | 1 |
| 1025 | Team F | Employee F2 | 2016 | 1 |
Is there a way to do this? Thank you so much for your help. Please let me know if you have any question.
