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

Highlighted
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

Highlighted
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

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") 😉