Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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;

View solution in original post

11 Replies
Anonymous
Not applicable
Author

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;

Not applicable
Author

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 applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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.

Not applicable
Author

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
Not applicable
Author

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.

Not applicable
Author

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
Not applicable
Author

06:53