Calculating downtime

[#1]

Hi I trying to calculate the downtime of a machine. But I need some help.

My data looks like this:

PTG16TIME	PTG16Status	RunStatus	ERR_NR
14-10-2015 09:12	START	1	235232
14-10-2015 09:31	CALLSTART	1	235232
14-10-2015 09:43	CALLEND	1	235232
14-10-2015 09:49	CALLSTART	1	235237
14-10-2015 09:54	STOP	0	235237
14-10-2015 10:12	START	1	235237
14-10-2015 10:13	STOP	0	235237
14-10-2015 10:35	START	1	235237
14-10-2015 10:38	STOP	0	235237
14-10-2015 10:40	START	1	235237
14-10-2015 10:41	CALLEND	1	235237
15-10-2015 08:49	STOP	0	235541
15-10-2015 08:59	CALLSTART	0	235541
15-10-2015 11:05	CALLEND	0	235541
15-10-2015 12:20	START	1	235573
15-10-2015 12:20	CALLSTART	1	235573
15-10-2015 12:30	STOP	0	235573
15-10-2015 12:31	START	1	235573
15-10-2015 12:35	CALLEND	1	235573
16-10-2015 09:22	STOP	0	235874
16-10-2015 09:31	CALLSTART	0	235874
16-10-2015 10:22	CALLEND	0	235874

Here is an example of what i need (Err: 235573)

Operator calls technician at 15-10-2015 08:59 the machine is still running  (Started 15-10-2015 12:20)

The machine stops at 15-10-2015 12:30 and is started again at 15-10-2015 12:31

The technician leaves at 15-10-2015 12:35

Between Callstart and Callend there are 15 minutes but running for 14 minutes. So the outcome should be 1 minute.

How to do this  ? I'm lost...

/Rasmus

[sunny_talwar]

Do you min 4 minutes? instead of 1 min?

[MarcoWedel]

Hi,

one solution might be:

tabErrs:
LOAD RecNo() as ID,
    Timestamp#(PTG16TIME,'DD-MM-YYYY hh:mm') as PTG16TIME,
    PTG16Status,
    RunStatus,
    ERR_NR
FROM [https://community.qlik.com/thread/236091] (html, codepage is 1252, embedded labels, table is @1);

Left Join (tabErrs)
LOAD ID,
    If(ERR_NR=Previous(ERR_NR),Interval(Previous(PTG16TIME)-PTG16TIME,'hh:mm')) as Duration
Resident tabErrs
Order By ERR_NR, PTG16TIME desc, ID desc;

hope this helps

regards

Marco

[#4]

No

CALLSTART: 15-10-2015 12:20

CALLEND: 15-10-2015 12:35

That is 15 minutes

Within the "CALL" time the machine has been running some of the time:

START:   Sometime before CALLSTART -> Therefor Time is set to CALLSTART (15-10-2015 12:20)

STOP:     15-10-2015 12:30

and

START:     15-10-2015 12:31

STOP:      Sometime after CALLEND -> Therefor time is set to CALLEND (15-10-2015 12:35)

This gives:

15 minutes - (10 min + 4 min) = 1 minute downtime (CALLTIME) where the machine has not been running.

Hope that makes sense...

[MarcoWedel]

like this:

hope this helps

regards

Marco