6 Replies Latest reply: May 20, 2009 7:57 PM by John Witherspoon RSS

    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.



        • Filter table based on analysis/comparing data
          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.



            • Filter table based on analysis/comparing data
              John Witherspoon

              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

              ;

                • Filter table based on analysis/comparing data
                  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.