11 Replies Latest reply: Nov 29, 2012 1:35 AM by guoxiang RSS

    Is this even possible using SQL statement in QlikView script?

      Below are example of data collected from a gantry door: (came from a single database table from MS Access [DoorAccess] but i split them out in QlikView)

       

      Entry table

       

      EntryDateStaffNameTimeInMessage
      21/11/12John07:00:00IN
      21/11/12Peter08:10:00IN
      21/11/12John09:20:00IN
      21/11/12Mary07:20:00IN
      22/11/12Joe07:00:00IN
      22/11/12Mike08:00:00IN
      23/11/12John07:00:00IN
      23/11/12Mary07:00:00IN
      23/11/12Mary08:00:00IN
      23/11/12Mary09:00:00IN

       

      Exit table

       

       

      ExitDateStaffNameTimeOutMessage
      21/11/12Peter18:10:00OUT
      21/11/12John18:00:00OUT
      21/11/12Peter18:17:00OUT
      21/11/12Mary19:10:00OUT
      22/11/12Joe18:20:00OUT
      23/11/12John18:10:00OUT
      23/11/12Mary17:30:00OUT

       

      What i wanted:

      Base on the two tables i need to derive the hours each staff spent time inside a certain area. However there are alot of noise data which i need clean up.

      There may have numerous timeIn or timeOut from the same person each day, so i want to get the earliest time in as the time In and the latest time out as the timeOut. Using the date and staff name as comparison and as identifier, i hope to achieve something like this in qlikview.

       

      The end result should be similar to this:

       

       

      DateStaffNameTimeInTimeOutHours
      21/11/12Peter08:10:0018:17:0010:07
      21/11/12John07:00:0018:00:0011:00
      21/11/12Mary07:20:0019:10:0011:50
      22/11/12Joe07:00:0018:20:0011:20
      22/11/12Mike08:00:00UnknownUnknown
      23/11/12John07:00:0018:10:0011:10
      23/11/12Mary07:00:0017:30:0010:50

       

      I know my question is very demanding but i really tried to achieve this for days. Try using subqueries, join, creation of third table but failed. Not familiar with syntax of Qlikview SQL. I really need help. Be it solutions or alternatives, please suggest. And i do not have DB rights so i cant do much on the database side, mainly on qlikview.

       

      God please help me,

      10e5x