Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Calculate Time (in mins) between Two Flags

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

TunIDDateTimedi1
100112/20/19 11:45 PM1
100112/21/19 3:47 AM1
100112/21/19 4:59 PM1
100112/21/19 5:08 PM0
100112/21/19 8:18 PM0
100112/21/19 9:31 PM1
100112/21/19 9:46 PM1
100112/22/19 2:07 AM1
100112/22/19 2:27 AM1
100112/22/19 2:31 AM1
100112/22/19 4:14 AM1
100112/22/19 9:18 AM0
100112/22/19 9:36 AM0
100112/22/19 9:49 AM1
100112/22/19 10:19 AM1
100112/22/19 10:34 AM1
100112/22/19 11:58 AM1
100112/22/19 1:19 PM0

 

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.

Thanks in advance

Labels (1)
4 Replies
Highlighted
Master
Master

Link

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
MVP
MVP

To build a perfect logic please provide data with expected output for multiple TunID

Highlighted
Partner
Partner

Kush,

The logic will be the same for each of the tunid.  You can just replicate the same and change the tunid.

Thanks.

Highlighted
MVP
MVP

T1:
LOAD
TunID,
DateTime,
di1
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);

T2:
Load *,
if((TunID= Previous(TunID) and di1=0 and Previous(di1)=1) or
(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;
Load *,
if((RowNo()=1 and di1=0) or (TunID<>Previous(TunID) and di1=0),'First Occurance of 0',
if(TunID= Previous(TunID) and di1=0 and Previous(di1)=1, 'First Occurance of 0 after 1',
if((RowNo()=1 and di1=1) or (TunID<>Previous(TunID) and di1=1),'First Occurance of 1',
if(TunID= Previous(TunID) and di1=1 and Previous(di1)=0,'First Occurance of 1 after 0',Peek(Status))))) as Status,

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',
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,

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,
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
Resident T1
Order by TunID,DateTime;

Drop Table T1;

T3:
Load *,
if(len(trim(DATE_DIFF_MIN1))=0, Peek(DATE_DIFF_MIN),DATE_DIFF_MIN1) as DATE_DIFF_MIN
Resident T2
Order by TunID,DateTime desc ;

Drop Table T2;

Drop Fields DATE_DIFF,DATE_DIFF_MIN1;