Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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;
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;
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.