Is this possible to do in Qlikview?
This is the original table loaded from database.
Variable | Status | Date | Duration |
A | StatusA | 9/10/2017 18:30:00.00 | 4 |
A | StatusB | 9/10/2017 23:30:00.00 | 5 |
B | StatusA | 9/10/2017 10:00:00.00 | 8 |
B | StatusB | 9/10/2017 21:45:00.00 | 9 |
And how I want this to process.
Variable | Status | Date | Duration | EndDate |
A | StatusA | 9/10/2017 18:30:00.00 | 4 | 9/10/2017 22:30:00.00 |
A | StatusB | 9/10/2017 23:30:00.00 | 0.5 | 9/10/2017 23:59:59.59 |
A | StatusB | 9/11/2017 0:00:00.00 | 4.5 | 9/11/2017 3:30:00.00 |
B | StatusA | 9/10/2017 10:00:00.00 | 8 | 9/10/2017 18:00:00.00 |
B | StatusB | 9/10/2017 21:45:00.00 | 2.25 | 9/10/2017 23:59:59.59 |
B | StatusB | 9/11/2017 0:00:00.00 | 6.75 | 9/11/2017 6:15:00.00 |
I know this is possible through resident table but how to check if the variable's running time exceeded a day then creates another row to separate the duration to next day. This is the case because I have a filter by week so if the last day (or Saturday) has a variable with exceeded duration, data won't be accurate.
The result data will need to get the total duration per variable which is filtered by week.