Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
jsn
Honored Contributor

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

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;

11 Replies
jsn
Honored Contributor

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

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

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

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

jsn
Honored Contributor

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

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

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

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.

jsn
Honored Contributor

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

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

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

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

jsn
Honored Contributor

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

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

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

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?

jsn
Honored Contributor

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

06:53