topic Calculate Time (in mins) between Two Flags in New to Qlik Sense
https://community.qlik.com/t5/New-to-Qlik-Sense/Calculate-Time-in-mins-between-Two-Flags/m-p/1659984#M154057
<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. How can we use the Peek in this regards.</P><P>Thanks in advance</P>Sun, 22 Dec 2019 13:46:22 GMTsjhussain2019-12-22T13:46:22ZCalculate Time (in mins) between Two Flags
https://community.qlik.com/t5/New-to-Qlik-Sense/Calculate-Time-in-mins-between-Two-Flags/m-p/1659984#M154057
<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. How can we use the Peek in this regards.</P><P>Thanks in advance</P>Sun, 22 Dec 2019 13:46:22 GMThttps://community.qlik.com/t5/New-to-Qlik-Sense/Calculate-Time-in-mins-between-Two-Flags/m-p/1659984#M154057sjhussain2019-12-22T13:46:22ZRe: Calculate Time (in mins) between Two Flags
https://community.qlik.com/t5/New-to-Qlik-Sense/Calculate-Time-in-mins-between-Two-Flags/m-p/1660410#M154173
<P><A href="https://community.qlik.com/t5/QlikView-Scripting/Calculate-Time-duration-between-Rows/m-p/1660056#M97791" target="_self">Link</A></P>Tue, 24 Dec 2019 09:50:35 GMThttps://community.qlik.com/t5/New-to-Qlik-Sense/Calculate-Time-in-mins-between-Two-Flags/m-p/1660410#M154173Taoufiq_Zarra2019-12-24T09:50:35ZRe: Calculate Time (in mins) between Two Flags
https://community.qlik.com/t5/New-to-Qlik-Sense/Calculate-Time-in-mins-between-Two-Flags/m-p/1660459#M154185
<P>To build a perfect logic please provide data with expected output for multiple TunID</P>Tue, 24 Dec 2019 11:55:51 GMThttps://community.qlik.com/t5/New-to-Qlik-Sense/Calculate-Time-in-mins-between-Two-Flags/m-p/1660459#M154185Kush2019-12-24T11:55:51ZRe: Calculate Time (in mins) between Two Flags
https://community.qlik.com/t5/New-to-Qlik-Sense/Calculate-Time-in-mins-between-Two-Flags/m-p/1660480#M154189
<P>Kush,</P><P>The logic will be the same for each of the tunid. You can just replicate the same and change the tunid.</P><P>Thanks.</P>Tue, 24 Dec 2019 12:51:56 GMThttps://community.qlik.com/t5/New-to-Qlik-Sense/Calculate-Time-in-mins-between-Two-Flags/m-p/1660480#M154189sjhussain2019-12-24T12:51:56ZRe: Calculate Time (in mins) between Two Flags
https://community.qlik.com/t5/New-to-Qlik-Sense/Calculate-Time-in-mins-between-Two-Flags/m-p/1660544#M154201
<P>T1:<BR />LOAD<BR />TunID,<BR />DateTime,<BR />di1<BR />FROM [lib://Qlik web]<BR />(html, utf8, embedded labels, table is @1);</P><P>T2:<BR />Load *,<BR />if((TunID= Previous(TunID) and di1=0 and Previous(di1)=1) or<BR />(TunID= Previous(TunID) and di1=1 and Previous(di1)=0), (DATE_DIFF-Previous(DATE_DIFF))*24*60,Peek(DATE_DIFF_MIN1)) as DATE_DIFF_MIN1;<BR />Load *,<BR />if((RowNo()=1 and di1=0) or (TunID<>Previous(TunID) and di1=0),'First Occurance of 0',<BR />if(TunID= Previous(TunID) and di1=0 and Previous(di1)=1, 'First Occurance of 0 after 1',<BR />if((RowNo()=1 and di1=1) or (TunID<>Previous(TunID) and di1=1),'First Occurance of 1',<BR />if(TunID= Previous(TunID) and di1=1 and Previous(di1)=0,'First Occurance of 1 after 0',Peek(Status))))) as Status,</P><P>if((TunID= Previous(TunID) and di1=0 and Previous(di1)=1) or (RowNo()=1 and di1=1) or (TunID<>Previous(TunID) and di1=1), 'Moving',<BR />if(TunID= Previous(TunID) and di1=1 and Previous(di1)=0 or (RowNo()=1 and di1=0) or (TunID<>Previous(TunID) and di1=0), 'Stopped',Peek(Flag))) as Flag,<BR /><BR />if((TunID= Previous(TunID) and di1=0 and Previous(di1)=1) or (RowNo()=1 and di1=1) or (TunID<>Previous(TunID) and di1=1), DateTime,<BR />if(TunID= Previous(TunID) and di1=1 and Previous(di1)=0 or (RowNo()=1 and di1=0) or (TunID<>Previous(TunID) and di1=0), DateTime,Peek(DATE_DIFF))) as DATE_DIFF<BR />Resident T1<BR />Order by TunID,DateTime;</P><P>Drop Table T1;</P><P>T3:<BR />Load *,<BR />if(len(trim(DATE_DIFF_MIN1))=0, Peek(DATE_DIFF_MIN),DATE_DIFF_MIN1) as DATE_DIFF_MIN<BR />Resident T2<BR />Order by TunID,DateTime desc ;</P><P>Drop Table T2;</P><P>Drop Fields DATE_DIFF,DATE_DIFF_MIN1;</P>Tue, 24 Dec 2019 18:43:44 GMThttps://community.qlik.com/t5/New-to-Qlik-Sense/Calculate-Time-in-mins-between-Two-Flags/m-p/1660544#M154201Kush2019-12-24T18:43:44Z