Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

GPS - Count the stop time

Hello Guys,

I need to create a expression to insert into a label.

In this expression, i have to use this data:

Date/hour, ID_Path, Ignition

So what i need to do is:

I need to count the amount of downtime. It tells the time between ignition 0 (off) and the Ignition 1 (on) = date/hour(ignition 1) - date/hour(ignition 0)

The problem is I have several identical / different records.

                             

Date/Hour,     Ignition id_path,  
2014-09-08 00:16:32.000017215967
2014-09-08 00:36:42.000017211123
2014-09-08 00:56:52.000017204958
2014-09-08 01:17:01.000017259288
2014-09-08 01:37:11.000017258500
2014-09-08 01:57:21.000017257560
2014-09-08 02:04:47.000117257350
2014-09-08 02:05:12.000117260469
2014-09-08 02:05:36.000117259887
2014-09-08 02:05:54.00010206088
2014-09-08 02:06:35.00013348442
2014-09-08 02:08:29.000115325993
2014-09-08 02:08:34.000113646438
2014-09-08 02:08:46.00013773396
2014-09-08 02:09:05.00014473620
2014-09-08 02:09:16.00006473289
2014-09-08 02:10:10.00015172137
2014-09-08 02:10:27.00019388413
2014-09-08 02:10:53.000117259864
2014-09-08 02:11:24.000117259854
2014-09-08 02:12:12.000117261094
2014-09-08 02:13:03.000117261092
2014-09-08 02:13:28.000117261081
2014-09-08 02:14:04.000117261068

I need to count the downtime of each ID_Path (to say the total downtime of everybody and the max downtime). The data is not sorted by id_path but by the Date / Hour in the file. So have no idea how to get the total downtime of all ID_Path and acquire the number of ID_Path bigger and less downtime.

Thanks !

11 Replies
JonnyPoole
Employee
Employee

how is down time computed ?   For an ID do you get the Date/Hour when ignition = 0 and count the days and hours until there is a row where ignition = 1 for that ID ?   This looks like a list of ignition status changes. But i don't think any of the IDs have 2 records where 1 record is 0 and another is 1... 

Anonymous
Not applicable
Author

Down time is the amount of time which a vehicle is turned off and turned on.

It is calculated always for Yesterday (but now i'm using a fictcial data). But this is calculated for only 1 day.

For an ID do you get the Date/Hour when ignition = 0 and count the days and hours until there is a row where ignition = 1 for that ID ?

Yes ! You are correct. But i need the total of all ID too.

This looks like a list of ignition status changes. But i don't think any of the IDs have 2 records where 1 record is 0 and another is 1...

Yes, its a status changes Time. In the example i've gave maybe dont have, BUT, it exists. And i need to know how to calculate it. I can set this in script or/and in the expression. The best way, i can do.

Thanks !

Morandi

JonnyPoole
Employee
Employee

Can the ID go from On to Off and back again ?  just wondering what the permutations are. thats important.  maybe if you have a sample set of data with all the different permutations that would be helpful.

Anonymous
Not applicable
Author

Only Ignition turn on (1) or off (0). The ID never changes.

Sample Attached. In the ID_PATH, use the function left(ID_PATH,5) to get the  correct information of ID_PATH.

Thanks !

MarcoWedel

Hi,

one possible solution:

tabData:

LOAD Timestamp(YearStart(Today())+Rand()*DayNumberOfYear(Today())) as [Date/Hour],

    Round(Rand()) as Ignition,

    10000000+Floor(Rand()*40) as [id_path]

AutoGenerate 1000;

tabDowntimes:

LOAD Previous([Date/Hour]) as [Date/Hour],

    Interval(EdgeTime-Previous(EdgeTime), 'dd hh:mm:ss') as Downtime

Where EdgeType='Rising' and [id_path]=Previous([id_path]);

LOAD [id_path],

    [Date/Hour],

    [Date/Hour] as EdgeTime,

    Pick((Ignition-Previous(Ignition))/2+1.5,'Falling','Rising') as EdgeType

Resident tabData

Where Ignition-Previous(Ignition)

Order By [id_path], [Date/Hour];

QlikCommunity_Thread_133815_Pic1.JPG.jpg

QlikCommunity_Thread_133815_Pic2.JPG.jpg

QlikCommunity_Thread_133815_Pic3.JPG.jpg

hope this helps

regards

Marco

MarcoWedel

or with your sample data:

QlikCommunity_Thread_133815_Pic4.JPG.jpg

tabData:

LOAD Ignition,

    Date#([Date/Hour], 'YYYY-MM-DD hh:mm:ss.fff') as [Date/Hour],

    Left(ID_PATH,5) as ID_PATH

FROM [http://community.qlik.com/servlet/JiveServlet/download/612276-127033/SAMPLE.xlsx] (ooxml, embedded labels, table is Plan1);

tabDowntimes:

LOAD Previous([Date/Hour]) as [Date/Hour],

    Interval(EdgeTime-Previous(EdgeTime), 'dd hh:mm:ss') as Downtime

Where EdgeType='Rising' and ID_PATH=Previous(ID_PATH);

LOAD ID_PATH,

    [Date/Hour],

    [Date/Hour] as EdgeTime,

    Pick((Ignition-Previous(Ignition))/2+1.5,'Falling','Rising') as EdgeType

Resident tabData

Where Ignition-Previous(Ignition)

Order By ID_PATH, [Date/Hour];

hope this helps

regards

Marco

Anonymous
Not applicable
Author

Hello Marco !

It seems that worked ! I'm gonna test and return to you !

Thanks for contribution ! Can you explain to me how are you doing it?

Didnt understand this line:

Pick((Ignition-Previous(Ignition))/2+1.5,'Falling','Rising') as EdgeType 


Morandi

Anonymous
Not applicable
Author

Hello Marco Wedel,

I'm trying now to see the average time of everybody. What i need is to see the time and distance of each ID_PATH.

To this i need the first "event = exit" and last "event = exit".

The time is calculated with [Date/Hour] and the distance, I have the data on how many KM it started and finished (final KM - KM beginning).

I need this in script. Can you help me?

Thanks !

MarcoWedel

it's just a shorter version of

If(Ignition=1 and Previous(Ignition)=0, 'Rising',

   If(Ignition=0 and Previous(Ignition)=1, 'Falling')) as EdgeType


regards


Marco