Is this even possible using SQL statement in QlikView script?

[#1]

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

EntryDate	StaffName	TimeIn	Message
21/11/12	John	07:00:00	IN
21/11/12	Peter	08:10:00	IN
21/11/12	John	09:20:00	IN
21/11/12	Mary	07:20:00	IN
22/11/12	Joe	07:00:00	IN
22/11/12	Mike	08:00:00	IN
23/11/12	John	07:00:00	IN
23/11/12	Mary	07:00:00	IN
23/11/12	Mary	08:00:00	IN
23/11/12	Mary	09:00:00	IN

Exit table

ExitDate	StaffName	TimeOut	Message
21/11/12	Peter	18:10:00	OUT
21/11/12	John	18:00:00	OUT
21/11/12	Peter	18:17:00	OUT
21/11/12	Mary	19:10:00	OUT
22/11/12	Joe	18:20:00	OUT
23/11/12	John	18:10:00	OUT
23/11/12	Mary	17:30:00	OUT

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:

Date	StaffName	TimeIn	TimeOut	Hours
21/11/12	Peter	08:10:00	18:17:00	10:07
21/11/12	John	07:00:00	18:00:00	11:00
21/11/12	Mary	07:20:00	19:10:00	11:50
22/11/12	Joe	07:00:00	18:20:00	11:20
22/11/12	Mike	08:00:00	Unknown	Unknown
23/11/12	John	07:00:00	18:10:00	11:10
23/11/12	Mary	07:00:00	17:30:00	10: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

[Anonymous · Accepted solution]

Something like this. Obviously you have to change the select statements to match your database table names etc.

T1:

Load

     *;

SQL Select

     Date,

StaffName,

Min(TimeIn) as TimeIn

From EntryTable

group by Date, StaffName;

join (T1)

Load

     *;

SQL Select

     Date,

StaffName,

Max(TimeOut) as TimeOut

From ExitTable

group by Date, StaffName;

T2:

Load

     *,

     interval(TimeOut-TimeIn, 'hh:mm') as Hours

Resident T1;

Drop table T1;

[#3]

OMG Johannes how u did that? It kinda works... just same as the pevious, it wont display the assoicated hours unless i do a select posible at the hours listbox. Even i a value at timeIn listBox and a value at timeOut listBox, it wouldnt show/highlight any value in white(all grey) then when i do the select possible, the correct hours was selected. Do u have any idea why is this happening?

With many thanks,

Guoxiang

[Anonymous]

Not sure, but if you can upload a small copy of the .qvw file to the thread I could take a look and see if I can figure out what's wrong

[#5]

haha i dont mind but as i have said many times, i just start using qlikview 2 days ago. How do i upload a small part to u? I just send u my .qvw? I have no rights to my the db else i dont mind let u take a look.

[Anonymous]

When you reply to a post here you can click "Use advanced editor" on the top right of the text box which allows you to upload files along with your post.

[#7]

haha i attached to the original post. Please kindly take a look, and let me know when u have got the file. i only want to share this with u

[Anonymous]

Seems to work fine here, except that there's some missing entry and exit times somewhere here and there, but I guess that's common with people coming and going at different times and sometimes it rolls over midnight etc.

If I pick a name, like G RAMATAS for example, I can see the entries and exits plus duration spent just fine.

[#9]

I chose the same field as yours but i cant see the duration spent...thats very weird..... U try selecting G RAMATAS then followed by 01/05/2012 for the date. What do u see at the hours listBox?

[Anonymous]

06:53