Forum Discussion
Fill up or down based on conditions
Hello everyone,
I need to complete some blanks in a table based on an id_employee and an id_week. If the first registry (by id_week) of an employee is blank it should be filled up with the following category of this employee, in the case that the blank registry is the last one of an employee, it should be filled down with the previous category.
Here an example table of how it should look and the data table:
| id_employee | id_week | category |
| 1 | 1 | |
| 1 | 2 | a |
| 1 | 3 | a |
| 1 | 4 | b |
| 1 | 5 | |
| 2 | 3 | a |
| 2 | 4 | b |
| 2 | 5 | c |
| 2 | 6 | |
| 3 | 2 | |
| 3 | 3 | b |
| 3 | 4 | c |
The easy (but ugly) way of doing this is to:
1. Create 3 index columns starting at 1,2,3
2. Do a self merge with index column 2 and index column 1 as the join columns
3. Do the same for index column 2 and index column 3.
4. Expand the join columns.
5. Rename the 2nd weekcategory column (the first join result) as "Prevweekcategory" similar for the id_employee
6.Rename the 2nd weekcategory column (the first join result) as "Nextweekcategory" similar for the id_employee
Now add a new column "Fixedweekcategory" with the logic to pick either the appropiate weekcategory based on if the current weekcategory is blank and if the current employee id matches the next or previous employee id.
1 Reply
The easy (but ugly) way of doing this is to:
1. Create 3 index columns starting at 1,2,3
2. Do a self merge with index column 2 and index column 1 as the join columns
3. Do the same for index column 2 and index column 3.
4. Expand the join columns.
5. Rename the 2nd weekcategory column (the first join result) as "Prevweekcategory" similar for the id_employee
6.Rename the 2nd weekcategory column (the first join result) as "Nextweekcategory" similar for the id_employee
Now add a new column "Fixedweekcategory" with the logic to pick either the appropiate weekcategory based on if the current weekcategory is blank and if the current employee id matches the next or previous employee id.