topic Re: How to create a sum of total time using a timestamp in QlikView

How to create a sum of total time using a timestamp

Wed, 10 Jul 2013 08:21:42 GMT

Please excuse any ignorance as I have only been working with Qlikview a week.

I am looking to total up occurences of a given state based on the time stamp. For example.

If a car was travelling:

State Time_Stamp

Not Moving 10:44:11

Not Moving 10:44:15

Not Moving 10:44:32

Moving 10:45:01

Moving 10:45:23

Moving 10:45:32

Engine Off 10:46:22

Engine Off 10:53:21

Not Moving 10:54:02

Not Moving 10:54:32

Moving 10:54:33

Moving 10:55:23

Essentially, the time at which the state changes is the value I am concerned with, as all the points inbetween become irrelevant.

Not Moving 10:44:11

Moving 10:45:01 total time Not Moving in the first occurence is therefore = 42 seconds

Engine Off 10:46:22 total time Moving in the first occurence is therefore = 81 seconds

I would want to do this for every occurence and then take a total of the number of seconds spent in each state so that I could graph state by time spent in that state.

At this stage I don't mind if I use the load script or graph expression to complete the calculation, truthfully I am more concerned if it is possible within Qlikview.

Re: How to create a sum of total time using a timestamp

swuehl — Wed, 10 Jul 2013 08:54:57 GMT

You can use something like this in the script:

TEST:

LOAD * INLINE [

State, Time_Stamp

Not Moving, 10:44:11

Not Moving, 10:44:15

Not Moving, 10:44:32

Moving, 10:45:01

Moving, 10:45:23

Moving, 10:45:32

Engine Off, 10:46:22

Engine Off, 10:53:21

Not Moving, 10:54:02

Not Moving, 10:54:32

Moving, 10:54:33

Moving, 10:55:23

];

TMP1:

LOAD *,

if(Peek(State) <> State,

Time_Stamp,

peek(StateStart)) as StateStart

Resident TEST order by Time_Stamp asc;

DROP Table TEST;

TMP2:

LOAD *,

if(Peek(State) <> State,

interval(StateEnd-StateStart)) as StateInterval;

LOAD *,

if(Peek(State) <> State,

peek(Time_Stamp),

peek(StateEnd)) as StateEnd,

AutoNumber(StateStart) as StateNr

Resident TMP1 order by Time_Stamp desc;

Drop table TMP1;

RESULT:

LOAD State,

interval(sum(StateInterval)) as SumStateInterval

Resident TMP2

group by State;

Re: How to create a sum of total time using a timestamp

Wed, 10 Jul 2013 09:55:13 GMT

Works perfectly, thank you.

I did however leave off the fact that there can be additional cars. However, from your code I tried putting CarNumber in the order by and group by statement at the end in the hope it would group the time stamps by CarNumber in the first instance and then show the time spent in each Navigation State for each. However, I had no such luck it didn't seem to work for me.

Would it be a case of introducing another If(Peak(CarNumber = CarNumber) to catch what CarNumber changes also.

Your help so far has been fantastic and any help here will save me time as I am still getting my head around the Qlikview syntax and coding structure.

Modified Code:

TMP1:

LOAD *,

if(Peek(State) <> State,

Time_Stamp,

peek(StateStart)) as StateStart

Resident TEST order by CarNumber, Time_Stamp asc;

DROP Table TEST;

TMP2:

LOAD *,

if(Peek(State) <> State,

interval(StateEnd-StateStart)) as StateInterval;

LOAD *,

if(Peek(State) <> State,

peek(Time_Stamp),

peek(StateEnd)) as StateEnd,

AutoNumber(StateStart) as StateNr

Resident TMP1 order by CarNumber, Time_Stamp desc;

Drop table TMP1;

RESULT:

LOAD State,

interval(sum(StateInterval)) as SumStateInterval

Resident TMP2

group by CarNumber, State;

Re: How to create a sum of total time using a timestamp

swuehl — Wed, 10 Jul 2013 10:08:20 GMT

Yes, you need to check for car changes, too.

Maybe like this:

TEST:

LOAD * INLINE [

State, Time_Stamp, Car

Not Moving, 10:44:11,1

Not Moving, 10:44:15,1

Not Moving, 10:44:32,1

Moving, 10:45:01,1

Moving, 10:45:23,1

Moving, 10:45:32,1

Engine Off, 10:46:22,1

Engine Off, 10:53:21,1

Not Moving, 10:54:02,1

Not Moving, 10:54:32,1

Moving, 10:54:33,1

Moving, 10:55:23,1

Not Moving, 10:44:11,2

Not Moving, 10:44:15,2

Not Moving, 10:44:32,2

Moving, 10:45:01,2

Moving, 10:45:23,2

Moving, 10:45:32,2

Engine Off, 10:46:22,2

Engine Off, 10:53:21,2

Not Moving, 10:54:02,2

Not Moving, 10:54:32,2

Moving, 10:54:33,2

Moving, 10:55:23,2

];

TMP1:

LOAD *,

if(Peek(State) <> State or Peek(Car)<>Car,

Time_Stamp,

peek(StateStart)) as StateStart

Resident TEST order by Car, Time_Stamp asc;

DROP Table TEST;

TMP2:

LOAD *,

if(Peek(State) <> State and Peek(Car) = Car,

interval(StateEnd-StateStart)) as StateInterval;

LOAD *,

if(Peek(State) <> State,

if(Peek(Car) = Car, peek(Time_Stamp)),

peek(StateEnd)) as StateEnd,

AutoNumberHash128(Car,StateStart) as StateNr

Resident TMP1 order by Car, Time_Stamp desc;

Drop table TMP1;

RESULT:

LOAD Car,

State,

interval(sum(StateInterval)) as SumStateInterval

Resident TMP2

group by Car, State;

Re: How to create a sum of total time using a timestamp

Wed, 10 Jul 2013 10:17:19 GMT

Perfect again! Does exactly what I need!

Thank you for getting back to me so quickly. I should be able to get my head around it all much quicker with working code to reference.