Calculate Time duration between Rows

[malimohammed]

Hello,

Hope all is well with everyone.

We are working on a fleet management system and have a scenario in which we have to sum the time (in mins) for Engine On and Engine Off.

We have 3 fields - TunID, DateTime, di1

What we want to do is for each TunID we would to calculate the time between

* when di1=1 and di1=0 - Engine On
* when di1=0 and di1=1 - Engine Off

Please find attached the excel sheet showing the logic and how it is calculated

TunID	DateTime	di1
1001	12/20/19 11:45 PM	1
1001	12/21/19 3:47 AM	1
1001	12/21/19 4:59 PM	1
1001	12/21/19 5:08 PM	0
1001	12/21/19 8:18 PM	0
1001	12/21/19 9:31 PM	1
1001	12/21/19 9:46 PM	1
1001	12/22/19 2:07 AM	1
1001	12/22/19 2:27 AM	1
1001	12/22/19 2:31 AM	1
1001	12/22/19 4:14 AM	1
1001	12/22/19 9:18 AM	0
1001	12/22/19 9:36 AM	0
1001	12/22/19 9:49 AM	1
1001	12/22/19 10:19 AM	1
1001	12/22/19 10:34 AM	1
1001	12/22/19 11:58 AM	1
1001	12/22/19 1:19 PM	0

will appreciate if someone can share the logic how we can do it in Qlik.  For each block of 1&0 we have to look at the first occurrence of 1 and first occurrence of 0 and then calculate the time from 1 to 0.

Thanks in advance

[Taoufiq_Zarra · Accepted solution]

Hi,

a good challenge 🙂

attached the qlikview file.
the code is a little long, but it's to explain the logic, otherwise you can optimize it.

the result :

otherwise the load script is :

Input:

LOAD 
    RowNo() as ID,
    tunid, 
    Date(datetime,'DD/MM/YYYY hh:mm:ss') as datetime, 
    di1
FROM
.\DATA_COM.xlsx
(ooxml, embedded labels, table is Feuil1);

temps1:
Noconcatenate
LOAD
*,
if(ID=1,'First Occurance of 1',if((di1=1 and previous(di1)=0),'First Occurance of 1 after 0',if((di1=0 and previous(di1)=1),'First Occurance of 0 after 1'))) as Flag1,
if(ID=1,'0',if((di1=1 and previous(di1)=0),'1',if((di1=0 and previous(di1)=1),'2'))) as FlagNum,
if(di1=1,'Moving','Stoped') as Flag2

    resident Input
    order by ID;

    DROP TABLE Input;

temps2:
Noconcatenate
LOAD
*,

if(FlagNum='0',datetime,if(FlagNum='1' or FlagNum='2',(datetime))) as ToDate,

if(FlagNum='0',ID,if(FlagNum='1' or FlagNum='2',(ID))) as FlagRow

    resident temps1
    order by ID;

    DROP TABLE temps1;

temps3:
Noconcatenate
LOAD
distinct FlagRow,Flag1,ToDate

    resident temps2
 order by FlagRow;

    DROP TABLE temps2;

 
 Final:
Noconcatenate
LOAD *,
num(if(FlagRow<>1,(ToDate-previous(ToDate))*60*24),'###0,00') as Duration

    resident temps3
order by FlagRow;

    DROP TABLE temps3;

[RUNIL · Accepted solution]

Thanks Tauofiq,

Can we go like this?

t1:
LOAD
tunid,
datetime,
di1,
IF(di1<>Peek(di1) ,datetime) as Cal_Date
FROM [lib://DATASOURCE/LogFile.xlsx]
(ooxml, embedded labels, table is Sheet1) where IF(di1<>Peek(di1) ,datetime)>1;
//group by tunid,datetime, di1;

Load *,

num(Interval(Cal_Date-Previous(Cal_Date))*60*24,'###0,00') as TimeDiff Resident t1;

Drop Table t1;

[Taoufiq_Zarra]

you need to verify the expected outpout 👍

[RUNIL]

Output verified . Its exactly same. 

[Taoufiq_Zarra]

Awesome,
you can accept both solutions as 'Solution'