topic Re: Breakdown time in QlikView

Breakdown time

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

Hi all,

I want to calculate the breakdown time

ex:the breakdown time for machine 1 is 10 min

MachineNumber	Start	Stop
1	07:30	08:00
1	08:10	08:50
2	08:30	08:40
2	09:15	09:40
3	09:47	10:23
3	10:35	11:05
3	11:22	11:52

Re: Breakdown time

m_woolf — Mon, 06 Jun 2016 11:58:39 GMT

in script:

Stop - Start as BDTime;

the sum(BDTime) for each machine

Re: Breakdown time

ajsjoshua — Mon, 06 Jun 2016 12:13:05 GMT

Hi,

It should be like this

second one start -first one stop

MachineNumber	Start	Stop	Result
1	07:30	08:00	-
1	08:10	08:50	00:10:00
1	08:55	09:10	00:05:00
1	09:15	09:40	00:00:00

Re: Breakdown time

sunny_talwar — Mon, 06 Jun 2016 12:22:02 GMT

Not sure I understand the logic behind deriving the Result field. Would you be able to walk us through with the calculation there?

Re: Breakdown time

ajsjoshua — Mon, 06 Jun 2016 12:29:45 GMT

Hi Sunny,

I want to find the

machine breakdown time

You can see the machine started at 07:30 and stopped at 08:00 and resumed at 08:10 so the breakdown time is 10 min.

MachineNumber	Start	Stop	Breakdowntime
1	07:30	08:00	00:10:00
1	08:10	08:50	00:10:00
1	08:55	09:10	00:05:00
1	09:15	09:40	00:00:00

Re: Breakdown time

crusader_ — Mon, 06 Jun 2016 12:41:31 GMT

Hi,

Try this

LOAD *

, if(MachineNumber=previous(MachineNumber), Start - previous(Stop)) as Breakdowntime

;

[Your load statement]

HTH

Andrei

Re: Breakdown time

Peter_Cammaert — Mon, 06 Jun 2016 12:55:11 GMT

And first make sure that your source table is ordered by MachineNumber and ascending Start time.

Best,

Peter

Re: Breakdown time

Mon, 06 Jun 2016 12:55:14 GMT

TimeStamp#(Coulmn1-Coulmn2, 'DD-MM-YYYY hh:mm:ss') as ResultDateTime

Re: Breakdown time

crusader_ — Mon, 06 Jun 2016 12:57:10 GMT

Exactly

Re: Breakdown time

ajsjoshua — Mon, 06 Jun 2016 13:09:10 GMT

Hi Andrei ,

Its working for all values but in somecase its not working

MachineNumber	Start	Stop	Program	Breakdowntime
1	07:30	08:00	Lower Arm	00:10:00
1	08:10	08:50	Lower Arm	00:25:00
1	08:30	08:40	DETECT STOP	00:00:00
1	09:15	09:40	Lower Arm	00:07:00
1	09:47	10:23	Lower Arm	00:00:00

IF DETECT STOP comes it should not consider it.

Regards,

Joshua.

Re: Breakdown time

ajsjoshua — Mon, 06 Jun 2016 13:31:14 GMT

Hi Peter Cammaert crusader_stalwar1

Suggest me some ideas

MachineNumber	Start	Stop	Program	Breakdowntime
1	07:30	08:00	Lower Arm	00:10:00
1	08:10	08:50	Lower Arm	00:25:00
1	08:30	08:40	DETECT STOP	00:00:00
1	09:15	09:40	Lower Arm	00:07:00
1	09:47	10:23	Lower Arm	00:00:00

IF DETECT STOP comes it should not consider it.

Regards,

Joshua.

Re: Breakdown time

sunny_talwar — Mon, 06 Jun 2016 13:35:28 GMT

May be this:

Table:

LOAD * INLINE [

MachineNumber, Start, Stop, Program, Breakdowntime

1, 07:30, 08:00, Lower Arm, 00:10:00

1, 08:10, 08:50, Lower Arm, 00:25:00

1, 08:30, 08:40, DETECT STOP, 00:00:00

1, 09:15, 09:40, Lower Arm, 00:07:00

1, 09:47, 10:23, Lower Arm, 00:00:00

];

Left Join (Table)

LOAD MachineNumber,

Start,

Stop,

Interval(If(MachineNumber = Previous(MachineNumber), Peek('Start') - Stop)) as Breakdowntime_Calculated

Resident Table

Where Program <> 'DETECT STOP'

Order By MachineNumber, Start desc;

Re: Breakdown time

sunny_talwar — Mon, 06 Jun 2016 13:38:58 GMT

Or this to get 0's for where I currently have nulls

Table:

LOAD * INLINE [

MachineNumber, Start, Stop, Program, Breakdowntime

1, 07:30, 08:00, Lower Arm, 00:10:00

1, 08:10, 08:50, Lower Arm, 00:25:00

1, 08:30, 08:40, DETECT STOP, 00:00:00

1, 09:15, 09:40, Lower Arm, 00:07:00

1, 09:47, 10:23, Lower Arm, 00:00:00

];

Left Join (Table)

LOAD MachineNumber,

Start,

Stop,

Interval(If(MachineNumber = Previous(MachineNumber), Peek('Start') - Stop)) as Breakdowntime_Calculated

Resident Table

Where Program <> 'DETECT STOP'

Order By MachineNumber, Start desc;

FinalTable:

NoConcatenate

LOAD MachineNumber,

Start,

Stop,

Program,

Breakdowntime,

Interval(Alt(Breakdowntime_Calculated, 0)) as Breakdowntime_Calculated

Resident Table;

DROP Table Table;

Re: Breakdown time

ajsjoshua — Mon, 06 Jun 2016 13:43:58 GMT

Hi Sunny,

Excellant work

Its working Bro

Thank u.