Hi Community,
Hope everyone is doing well.
I'm struggling to split rows based on condition. Following is the format of data;
| Employee Code | Leave Start Date | Leave End Date | Hours | Period End Date | start week | Days diff | Net work days |
| 938 | 5/10/2020 | 23/10/2020 | 75 | 23/10/2020 | 41 | 18 | 15 |
| 884 | 18/12/2020 | 1/01/2021 | 37.5 | 1/01/2021 | 51 | 14 | 11 |
| 205 | 5/04/2021 | 19/04/2021 | 6 | 7/05/2021 | 15 | 14 | 11 |
| 498 | 5/12/2020 | 18/12/2020 | 75 | 18/12/2020 | 49 | 13 | 10 |
| 498 | 7/11/2020 | 20/11/2020 | 75 | 20/11/2020 | 45 | 13 | 10 |
I want to put a condition on days diff as follows;
Daysdiff > 14 = 3 rows
Daysdiff > 7 = 2 rows
Daydiff < 7 = 1 row
with the split, I need two columns such as "Start of week" and "Apportioned hours"
Following is what I expect to see as the result;
| Employee Code | Leave Start Date | Leave End Date | Hours | Period End Date | start week | Days diff | Net work days | Apportioned hours | Start of week |
| 938 | 5/10/2020 | 23/10/2020 | 75 | 23/10/2020 | 41 | 18 | 15 | 25 | 5/10/2020 |
| 938 | 5/10/2020 | 23/10/2020 | 75 | 23/10/2020 | 41 | 18 | 15 | 25 | 12/10/2020 |
| 938 | 5/10/2020 | 23/10/2020 | 75 | 23/10/2020 | 41 | 18 | 15 | 25 | 19/10/2020 |
Any help with this is highly appreciated.
Thank you in advance
Best regards,
Ashan
