topic Calculate Time duration between Rows in QlikView Scripting
https://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1659999#M97789
<P>Hello,</P><P>Hope all is well with everyone.</P><P>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.</P><P>We have 3 fields - TunID, DateTime, di1</P><P>What we want to do is for each TunID we would to calculate the time between</P><P>* when di1=1 and di1=0 - Engine On<BR />* when di1=0 and di1=1 - Engine Off</P><P> </P><P>Please find attached the excel sheet showing the logic and how it is calculated</P><TABLE border="1"><TBODY><TR><TD>TunID</TD><TD>DateTime</TD><TD>di1</TD></TR><TR><TD>1001</TD><TD>12/20/19 11:45 PM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/21/19 3:47 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/21/19 4:59 PM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/21/19 5:08 PM</TD><TD>0</TD></TR><TR><TD>1001</TD><TD>12/21/19 8:18 PM</TD><TD>0</TD></TR><TR><TD>1001</TD><TD>12/21/19 9:31 PM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/21/19 9:46 PM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 2:07 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 2:27 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 2:31 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 4:14 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 9:18 AM</TD><TD>0</TD></TR><TR><TD>1001</TD><TD>12/22/19 9:36 AM</TD><TD>0</TD></TR><TR><TD>1001</TD><TD>12/22/19 9:49 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 10:19 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 10:34 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 11:58 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 1:19 PM</TD><TD>0</TD></TR></TBODY></TABLE><P> </P><P>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.</P><P>Thanks in advance</P>Sun, 22 Dec 2019 19:12:25 GMTmalimohammed2019-12-22T19:12:25ZCalculate Time duration between Rows
https://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1659999#M97789
<P>Hello,</P><P>Hope all is well with everyone.</P><P>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.</P><P>We have 3 fields - TunID, DateTime, di1</P><P>What we want to do is for each TunID we would to calculate the time between</P><P>* when di1=1 and di1=0 - Engine On<BR />* when di1=0 and di1=1 - Engine Off</P><P> </P><P>Please find attached the excel sheet showing the logic and how it is calculated</P><TABLE border="1"><TBODY><TR><TD>TunID</TD><TD>DateTime</TD><TD>di1</TD></TR><TR><TD>1001</TD><TD>12/20/19 11:45 PM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/21/19 3:47 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/21/19 4:59 PM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/21/19 5:08 PM</TD><TD>0</TD></TR><TR><TD>1001</TD><TD>12/21/19 8:18 PM</TD><TD>0</TD></TR><TR><TD>1001</TD><TD>12/21/19 9:31 PM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/21/19 9:46 PM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 2:07 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 2:27 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 2:31 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 4:14 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 9:18 AM</TD><TD>0</TD></TR><TR><TD>1001</TD><TD>12/22/19 9:36 AM</TD><TD>0</TD></TR><TR><TD>1001</TD><TD>12/22/19 9:49 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 10:19 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 10:34 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 11:58 AM</TD><TD>1</TD></TR><TR><TD>1001</TD><TD>12/22/19 1:19 PM</TD><TD>0</TD></TR></TBODY></TABLE><P> </P><P>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.</P><P>Thanks in advance</P>Sun, 22 Dec 2019 19:12:25 GMThttps://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1659999#M97789malimohammed2019-12-22T19:12:25ZRe: Calculate Time duration between Rows
https://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1660056#M97791
<P>Hi,</P><P>a good challenge <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P>attached the qlikview file.<BR />the code is a little long, but it's to explain the logic, otherwise you can optimize it.</P><P>the result :</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 542px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/25910iD012A474534876EE/image-size/large?v=1.0&px=999" role="button" title="Capture.PNG" alt="Capture.PNG" /></span></P><P>otherwise the load script is :</P><LI-CODE lang="python">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;</LI-CODE><P> </P>Mon, 23 Dec 2019 10:22:10 GMThttps://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1660056#M97791Taoufiq_Zarra2019-12-23T10:22:10ZRe: Calculate Time duration between Rows
https://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1660438#M97806
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 999px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/25988iF53A9932FEE09C96/image-size/large?v=1.0&px=999" role="button" title="Capture.PNG" alt="Capture.PNG" /></span>Thanks Tauofiq,</P><P>Can we go like this?</P><P> </P><P>t1:<BR />LOAD<BR />tunid,<BR />datetime,<BR />di1,<BR />IF(di1<>Peek(di1) ,datetime) as Cal_Date<BR />FROM [lib://DATASOURCE/LogFile.xlsx]<BR />(ooxml, embedded labels, table is Sheet1) where IF(di1<>Peek(di1) ,datetime)>1;<BR />//group by tunid,datetime, di1;</P><P>Load *,</P><P>num(Interval(Cal_Date-Previous(Cal_Date))*60*24,'###0,00') as TimeDiff Resident t1;</P><P>Drop Table t1;</P><P> </P><P> </P>Tue, 24 Dec 2019 11:19:42 GMThttps://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1660438#M97806RUNIL2019-12-24T11:19:42ZRe: Calculate Time duration between Rows
https://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1660440#M97807
<P>you need to verify the expected outpout <span class="lia-unicode-emoji" title=":thumbs_up:">👍</span></P>Tue, 24 Dec 2019 11:01:51 GMThttps://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1660440#M97807Taoufiq_Zarra2019-12-24T11:01:51ZRe: Calculate Time duration between Rows
https://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1660451#M97808
<P>Output verified . Its exactly same. </P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 999px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/25989i27DAFCDCD77148BA/image-size/large?v=1.0&px=999" role="button" title="Capture.PNG" alt="Capture.PNG" /></span></P>Tue, 24 Dec 2019 11:21:13 GMThttps://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1660451#M97808RUNIL2019-12-24T11:21:13ZRe: Calculate Time duration between Rows
https://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1660455#M97809
<P>Awesome,<BR />you can accept both solutions as 'Solution'</P>Tue, 24 Dec 2019 11:26:02 GMThttps://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1660455#M97809Taoufiq_Zarra2019-12-24T11:26:02Z