Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Calculate Time duration between Rows

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 and then calculate the time from 1 to 0.

Thanks in advance

2 Solutions

Accepted Solutions
Highlighted
Master
Master

Hi,

a good challenge 🙂

attached the qlikview file.
the code is a little long, but it's to explain the logic, otherwise you can optimize it.

the result :

Capture.PNG

otherwise the load script is :

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;

 

Regards,
Taoufiq ZARRA

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

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

View solution in original post

Partner
Partner

Capture.PNGThanks Tauofiq,

Can we go like this?

 

t1:
LOAD
tunid,
datetime,
di1,
IF(di1<>Peek(di1) ,datetime) as Cal_Date
FROM [lib://DATASOURCE/LogFile.xlsx]
(ooxml, embedded labels, table is Sheet1) where IF(di1<>Peek(di1) ,datetime)>1;
//group by tunid,datetime, di1;

Load *,

num(Interval(Cal_Date-Previous(Cal_Date))*60*24,'###0,00') as TimeDiff Resident t1;

Drop Table t1;

 

 

View solution in original post

5 Replies
Highlighted
Master
Master

Hi,

a good challenge 🙂

attached the qlikview file.
the code is a little long, but it's to explain the logic, otherwise you can optimize it.

the result :

Capture.PNG

otherwise the load script is :

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;

 

Regards,
Taoufiq ZARRA

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

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

View solution in original post

Partner
Partner

Capture.PNGThanks Tauofiq,

Can we go like this?

 

t1:
LOAD
tunid,
datetime,
di1,
IF(di1<>Peek(di1) ,datetime) as Cal_Date
FROM [lib://DATASOURCE/LogFile.xlsx]
(ooxml, embedded labels, table is Sheet1) where IF(di1<>Peek(di1) ,datetime)>1;
//group by tunid,datetime, di1;

Load *,

num(Interval(Cal_Date-Previous(Cal_Date))*60*24,'###0,00') as TimeDiff Resident t1;

Drop Table t1;

 

 

View solution in original post

Highlighted
Master
Master

you need to verify the expected outpout 👍

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
Highlighted
Partner
Partner

Output verified . Its exactly same. 

 

Capture.PNG

Highlighted
Master
Master

Awesome,
you can accept both solutions as 'Solution'

Regards,
Taoufiq ZARRA

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

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