Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
sjhussain
Partner - Creator II
Partner - Creator II

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
Taoufiq_Zarra

Link

Regards,
Taoufiq ZARRA

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

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

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

sjhussain
Partner - Creator II
Partner - Creator II
Author

Kush,

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

Thanks.

Kushal_Chawda

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;