How to create a sum of total time using a timestamp

[#1]

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.

[swuehl · Accepted solution]

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;

[#3]

   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;

[swuehl]

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;

[#5]

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.