Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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. How can we use the Peek in this regards.
Thanks in advance
To build a perfect logic please provide data with expected output for multiple TunID
Kush,
The logic will be the same for each of the tunid. You can just replicate the same and change the tunid.
Thanks.
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;