Forum Discussion

lememins's avatar
lememins
Regular Visitor
7 years ago
Solved

Need Help - Calculating duration between two events

Hi all, 

 

I need help to calculate the time between two events. 

Our events come through with Factory | Area | Zone | Machine | Reason

so when an area stops it always has Reason = Machine Unavailable, when an area restarts a machine and reason is entered

Stop: F4|Setting|Area J||Area Unavailable

Start: F4|Setting|Area J|H40|Motor Fault

I want ot calculate the duration between the two timestamps - I can't calculate previous rows as there are multiple areas / zones that could stop in the same table. 

 

I've highlighted two examples in the table below where I've manually put in the duration but haven't figured out how to do this automatically. Hopefully then I can create visuals which display stop duration by machine, by area, by date etc... 

 

Thanks! 

DateTimeTypeDescriptionFactoryAreaZoneMachineReasonDateTimeIndexStop TimeDateTime.1Duration
20-Jun-1900:06:12EventF4|Setting|Area H||Area UnavailableF4SettingArea H Area Unavailable20/06/2019 00:06677020/06/2019 00:06 
20-Jun-1900:06:12EventF4|Setting|Area J||Area UnavailableF4SettingArea J Area Unavailable20/06/2019 00:06678020/06/2019 00:06 
20-Jun-1900:06:12EventF4|Setting|Area L||Area UnavailableF4SettingArea L Area Unavailable20/06/2019 00:06679020/06/2019 00:06 
20-Jun-1900:06:56EventF4|Setting|Area L|H40|Motor FaultF4SettingArea LH40Motor Fault20/06/2019 00:06676020/06/2019 00:06 
20-Jun-1900:07:00EventF4|Setting|Area J|H40|Motor FaultF4SettingArea JH40Motor Fault20/06/2019 00:07675020/06/2019 00:0700:01
20-Jun-1900:07:01EventF4|Setting|Area H|H40|Motor FaultF4SettingArea HH40Motor Fault20/06/2019 00:07674020/06/2019 00:07 
20-Jun-1900:07:49EventF4|Setting|Area H||Area UnavailableF4SettingArea H Area Unavailable20/06/2019 00:07673020/06/2019 00:07 
20-Jun-1900:08:01EventF4|Setting|Area H|J MAIN AIR SUPPLY|Layer ProblemF4SettingArea HJ MAIN AIR SUPPLYLayer Problem20/06/2019 00:08672020/06/2019 00:08 
20-Jun-1900:12:24EventF4|Press|Area K||Area UnavailableF4PressArea K Area Unavailable20/06/2019 00:12671020/06/2019 00:12 
20-Jun-1900:16:06EventF4|Press|Area K|K50|Suction Head InsertF4PressArea KK50Suction Head Insert20/06/2019 00:16670020/06/2019 00:1600:04

 

Need Help