Filter table based on analysis/comparing data

[peter_turner]

Hello Everyone
I have an interesting example and no idea how to start off....

I would like to produce a table which shows only 'Doors' which are 'Open' and don't have a matching 'Closed' statement, so they can be closed manually if needed.

Every time a door is opened it records a TimeStamp, DoorName and State.
State is 'Open' or 'Closed'
DoorName is a fixed text string per door.

I need to analyse the records, and match up all the 'Open' and 'Closed' states for a door, and only show doors which are in the Open state.

If possible i'd also like to have a calculated time field to show how long the door has been open.

Any suggestions and advice would be much appreciated!

Thanks,
Peter.

[peter_turner]

My initial thoughts are...

Filter the data to most recent at the top

Start with the first DoorName (1 of 10 possible names for example)

look at the first 'State' value for that Door.

If its 'Closed'
Then that Door is OK, ignore and move onto the next Door.

Else

If its 'Open' then flag it somehow.
Compare its timestamp with the current now(0) time, and produce a time value.

Move onto the next Door and repeat

This can lead onto producing average time between 'Open' and 'Closed' messages, and maybe even an alert if this calculated time is over a set threshold.

Thanks,
Peter.

[johnw]

Here's one approach that I think would work. It could probably be done somewhat simpler than this, though.

DoorLog:

LOAD DoorName,TimeStamp,State

<your source>

;

DoorState:

LOAD

DoorName

,max(TimeStamp) as TimeStamp

RESIDENT DoorLog

GROUP BY DoorName

;

LEFT JOIN LOAD DoorName,TimeStamp,State

RESIDENT DoorLog

;

DROP TABLE DoorLog

;

OpenDoor:

LOAD

DoorName

,TimeStamp

,now()-TimeStamp as Duration

RESIDENT DoorState

WHERE State = 'Open'

;

DROP TABLE DoorState

;

[peter_turner]

Hello John,

Thanks for the script, thats exactly what i was looking for as the first part of my example.
I'm still learning but think i understand what is happening...
DoorLog:
LOAD DoorName,TimeStamp,State
<your source>
;
/*
Normal load of data
*/

DoorState:
LOAD
DoorName
,max(TimeStamp) as TimeStamp
RESIDENT DoorLog
GROUP BY DoorName
;
/*
Group by DoorName and return the highest TimeStamp value for that grouping
*/


LEFT JOIN LOAD DoorName,TimeStamp,State
RESIDENT DoorLog
;
DROP TABLE DoorLog
;
/*
Add the 3 fields to the DoorState table, replace the old TimeStamp with the calulated value
*/

OpenDoor:
LOAD
DoorName
,TimeStamp
,now()-TimeStamp as Duration
RESIDENT DoorState
WHERE State = 'Open'
;
DROP TABLE DoorState
;
/*
Create a new table with all the data for viewing on screen
Only where the 'door' is open
*/

Developing this example forward, i need to try and create a table that looks like this:

DoorName StartTimeStamp Duration State
Front 14/05/2009 01:01:03 02:00:00 Open
Back 14/05/2009 01:01:02 00:02:00 Closed
Side 14/05/2009 01:01:01 00:03:00 Closed
Front 13/05/2009 23:50:03 00:01:00 Closed
(and all the other times a door has been 'open' 'closed'

I think this Would involve...
grouping the DoorName
calculating the time between a 'Open' event and the next 'Closed' event
If there was no 'Closed' event, then just show the time since the message (so someone can go fix the door)

My source data would be in a table looking like this:
DoorName TimeStamp State
Back 14/05/2009 02:02:02 Closed
Side 14/05/2009 02:02:01 Closed
Front 14/05/2009 01:01:03 Open
Back 14/05/2009 01:01:02 Open
Side 14/05/2009 01:01:01 Open

Any insight/ideas would be greatly appricated.

Thanks,
Peter.

[johnw · Accepted solution]

Something like the attached?

[peter_turner]

Hi John,

Thanks for the example file, thats the sort of thing i was looking for!

It's helped alot with my project, I just need to add afew more conditions on the If( statement to match 'doors', and thats sorted.

Thanks,
Peter.

[johnw]

Here's an alternative approach that may be a little simpler and more flexible. Someone was asking for something very similar to what you asked for, but where they could check the status as of any date, not just as of the current date. It might be a better approach for you as well as long as your data set isn't so large as to have performance problems doing a minor calculation in the chart. Something to consider, anyway.