topic Re: GPS - Count the stop time in QlikView

GPS - Count the stop time

Anonymous — Wed, 17 Sep 2014 18:35:59 GMT

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.000	0	17215967
2014-09-08 00:36:42.000	0	17211123
2014-09-08 00:56:52.000	0	17204958
2014-09-08 01:17:01.000	0	17259288
2014-09-08 01:37:11.000	0	17258500
2014-09-08 01:57:21.000	0	17257560
2014-09-08 02:04:47.000	1	17257350
2014-09-08 02:05:12.000	1	17260469
2014-09-08 02:05:36.000	1	17259887
2014-09-08 02:05:54.000	1	0206088
2014-09-08 02:06:35.000	1	3348442
2014-09-08 02:08:29.000	1	15325993
2014-09-08 02:08:34.000	1	13646438
2014-09-08 02:08:46.000	1	3773396
2014-09-08 02:09:05.000	1	4473620
2014-09-08 02:09:16.000	0	6473289
2014-09-08 02:10:10.000	1	5172137
2014-09-08 02:10:27.000	1	9388413
2014-09-08 02:10:53.000	1	17259864
2014-09-08 02:11:24.000	1	17259854
2014-09-08 02:12:12.000	1	17261094
2014-09-08 02:13:03.000	1	17261092
2014-09-08 02:13:28.000	1	17261081
2014-09-08 02:14:04.000	1	17261068

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 !

Re: GPS - Count the stop time

JonnyPoole — Wed, 17 Sep 2014 18:46:23 GMT

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...

Re: GPS - Count the stop time

Anonymous — Wed, 17 Sep 2014 19:17:59 GMT

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

Re: GPS - Count the stop time

JonnyPoole — Wed, 17 Sep 2014 19:23:02 GMT

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.

Re: Re: GPS - Count the stop time

Anonymous — Wed, 17 Sep 2014 20:36:33 GMT

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 !

Re: GPS - Count the stop time

MarcoWedel — Wed, 17 Sep 2014 21:47:49 GMT

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];

hope this helps

regards

Marco

Re: Re: GPS - Count the stop time

MarcoWedel — Wed, 17 Sep 2014 21:58:11 GMT

or with your sample data:

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

Re: GPS - Count the stop time

Anonymous — Mon, 22 Sep 2014 13:27:37 GMT

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

Re: GPS - Count the stop time

Anonymous — Mon, 22 Sep 2014 13:53:58 GMT

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 !

Re: GPS - Count the stop time

MarcoWedel — Mon, 22 Sep 2014 14:42:29 GMT

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

Re: GPS - Count the stop time

MarcoWedel — Mon, 22 Sep 2014 14:43:36 GMT

please mark this thread as answered if your initial question is answered and open another thread for this question.

thanks

regards

Marco

Re: GPS - Count the stop time

Anonymous — Mon, 22 Sep 2014 16:27:21 GMT

Thanks for the answer Marco !

Actually, it's a part of same problem. We will loose the context if i create another post. Can we continue here?

Thanks !