topic Re: Is this even possible using SQL statement in QlikView script? in QlikView

Is this even possible using SQL statement in QlikView script?

Thu, 29 Nov 2012 02:58:41 GMT

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

Re: Is this even possible using SQL statement in QlikView script?

Anonymous — Thu, 29 Nov 2012 03:14:13 GMT

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;

Re: Is this even possible using SQL statement in QlikView script?

Thu, 29 Nov 2012 03:40:07 GMT

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

Re: Is this even possible using SQL statement in QlikView script?

Anonymous — Thu, 29 Nov 2012 03:43:14 GMT

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

Re: Is this even possible using SQL statement in QlikView script?

Thu, 29 Nov 2012 03:46:15 GMT

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.

Re: Is this even possible using SQL statement in QlikView script?

Anonymous — Thu, 29 Nov 2012 03:48:28 GMT

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.

Re: Is this even possible using SQL statement in QlikView script?

Thu, 29 Nov 2012 03:50:57 GMT

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

Re: Is this even possible using SQL statement in QlikView script?

Anonymous — Thu, 29 Nov 2012 04:02:20 GMT

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.

Re: Is this even possible using SQL statement in QlikView script?

Thu, 29 Nov 2012 04:07:55 GMT

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?

Re: Is this even possible using SQL statement in QlikView script?

Anonymous — Thu, 29 Nov 2012 04:10:11 GMT

06:53

Re: Is this even possible using SQL statement in QlikView script?

Thu, 29 Nov 2012 04:15:56 GMT

Thats weird....yours can mine cant:( Did you change any settings or any other thigs?

Btw since we are at it, if u realize i have some logic loopholes. What i have done is only logical for staff that clocks in and out on the same day. Cuz the door is open for 24 hours, and staff do have 24 hour shift so they may clock in at 30/04/12 23:00:00 hours and clock out the next day.... Do you have any suggestions to retify this?

Thanks alot! really u are my saviour

Gratitude,

10e5x

Re: Is this even possible using SQL statement in QlikView script?

Thu, 29 Nov 2012 06:35:38 GMT

Hi Johannes,

With the same qvw u have, what should i do if i would like to display the field message_x. If i declare it at the select staement, i will need to group it too, which will spoilt the current function of the app.

Any suggestion?