topic Re: Calculate Time duration between Rows in QlikView

Calculate Time duration between Rows

malimohammed — Sun, 22 Dec 2019 19:12:25 GMT

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

Re: Calculate Time duration between Rows

Taoufiq_Zarra — Mon, 23 Dec 2019 10:22:10 GMT

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;

Re: Calculate Time duration between Rows

RUNIL — Tue, 24 Dec 2019 11:19:42 GMT

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;

Re: Calculate Time duration between Rows

Taoufiq_Zarra — Tue, 24 Dec 2019 11:01:51 GMT

you need to verify the expected outpout 👍

Re: Calculate Time duration between Rows

RUNIL — Tue, 24 Dec 2019 11:21:13 GMT

Output verified . Its exactly same.

Re: Calculate Time duration between Rows

Taoufiq_Zarra — Tue, 24 Dec 2019 11:26:02 GMT

Awesome,
you can accept both solutions as 'Solution'