How to iterate contents of table from another table?
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.
