topic Time in Status in QlikView

Time in Status

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a table with change times and new status change for various products.

Would it be possible to get a straight table calculating the accumulated time a product is in a status?

Example:

Source:

ID	Product	ChangeDate	NewStatus
1	A	20/07/17 13:00:00	Start
2	B	20/07/17 13:00:00	Start
3	A	21/07/17 08:00:00	Halt
4	C	22/07/17 12:00:00	Start
5	B	23/07/17 09:00:00	Halt
6	A	23/07/17 12:00:00	Start
7	B	23/07/17 13:00:00	Start
8	A	24/07/17 08:00:00	Stop

Result Straight Table:

Product	Status	Time in Status
A	Start	39:00:00
A	Halt	52:00:00
B	Start	68:00:00
B	Halt	4:00:00
C	Start	-

I would appreciate any help here.

Re: Time in Status

Anil_Babu_Samineni — Thu, 17 Aug 2017 15:07:00 GMT

Why Product - A and Status - Start ?? Why 39 hours rather 25 hours

Re: Time in Status

Anonymous — Thu, 17 Aug 2017 15:23:10 GMT

Product A is 2x in Status Start

ID1 to ID3 19:00:00

ID6 to ID8 20:00:00

Re: Time in Status

marcus_malinow — Thu, 17 Aug 2017 15:27:57 GMT

Try this:

_tmp:

LOAD * INLINE [

ID, Product, ChangeDate, NewStatus

1, A, 20/07/17 13:00:00, Start

2, B, 20/07/17 13:00:00, Start

3, A, 21/07/17 08:00:00, Halt

4, C, 22/07/17 12:00:00, Start

5, B, 23/07/17 09:00:00, Halt

6, A, 23/07/17 12:00:00, Start

7, B, 23/07/17 13:00:00, Start

8, A, 24/07/17 08:00:00, Stop

];

StatusDuration:

LOAD

ID, Product, ChangeDate, NewStatus,

if(Product = peek('Product', -1),

peek(ChangeDate) - ChangeDate) as Duration

RESIDENT _tmp

ORDER BY Product, ChangeDate desc;

Drop Table _tmp;

Re: Time in Status

Anil_Babu_Samineni — Thu, 17 Aug 2017 15:36:17 GMT

Are you expecting front end with out touch the script?

Re: Time in Status

Anonymous — Thu, 17 Aug 2017 15:48:27 GMT

That seem to work.

Too bad this has to be calculated during the load sequence of the data and can't be calculated in a way by Qlikview.