Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
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

Very demanding qns, sry but i need help. SQL statements.

Hi all,

I know this may not be totally related to QlikView but without extracting the correct data i cant even proceed.

I am faced with a very troubled scenario. I am given a .mdb from access 2003. The data are so messy and i am tasked to derive the working hrs. After trying for quite some time, with the help with some pros here, i thought i got it but after sometime i realize i made a serious mistake. Therefore have to post it here, hoping that some pro here can help me derive the sql statements.

TACS table:

EventDate     StaffName     EventTime     Message

21/11/12     John            23:01:00     OUT

21/11/12     Peter            23:10:00     IN

21/11/12     Peter            23:10:05     IN

22/11/12     John            07:19:00     IN

22/11/12     Peter            07:20:00     OUT

22/11/12     Joe            07:30:00     IN

22/11/12     Mike            08:00:00     IN

22/11/12     John            17:00:00     OUT

22/11/12     John            17:01:00     OUT

22/11/12     Joe            19:01:00     OUT

22/11/12     Mike            19:30:00     OUT

22/11/12     Mary            22:00:00     IN

23/11/12     John            07:01:00     IN

23/11/12     Mary            08:00:00     OUT

23/11/12     Peter            09:00:00     IN

What i want to derive.

FinalTable:

IDEntryDTExitDTStaffNameWorkingHrs
1Unknown 21/11/12 23:01:00JohnUnknown
221/11/12 23:10:0522/11/12 07:20:00Peter8hrs10mins
322/11/12 07:19:0022/11/12 17:00:00John9hrs41mins
422/11/12 07:30:0022/11/12 19:01:00Joe11hrs31min
522/11/12 08:00:00 22/11/12 19:30:00 Mike11hrs30min
622/11/12 22:00:0023/11/12 08:00:00Mary10hrs
723/11/12 07:01:00UnknownJohnUnknown
823/11/12 09:00:00UnknowPeterUnknown

If there are multiple clock in before out, i will use the latest clockIn as EntryDT, and vice versa, use the latest clockOut as ExitDT. However due to the mess, i dk how to write a statement to allow the prog to know which timing are for which event. Lastly if there are no respective clock in or out, it will be left unknown.

Any help will be greatly appreciated.

Thanks,

10e5x

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

Same script, but now with some comments. Probably easier to understand.

HIC

View solution in original post

4 Replies
hic
Former Employee
Former Employee

You need to do this in several steps using peek() and join. You'll find a solution in the attachment.

HIC

Not applicable
Author

Thank you so much, i asked this in many forums and only u replied. I need to digest your solutions... not familiar with QlikView sql syntax. I need to translate to normal sql syntax haha.

Thanks so much, i shall try this tml, let u know if it work out

Greatly appreciated,

10e5x

hic
Former Employee
Former Employee

Same script, but now with some comments. Probably easier to understand.

HIC

Not applicable
Author

Thank you so much for being so helpful.

I finally understood what u did. Just some slight changes to my requirement: i do not want to catagorise those with multiple clock in or out as invalid, instead i should choosethe latest clock in from the chunk of multiple in as my clockIn and latest out from the chunk as clockOut.

Thanks to your wonderful help, QlikView side is finally gg on.

Btw just trying my luck, are u good with sql statements? i need to do this in sql for ms access too. No obligation. Its out of topic for this forum or do u know of some good forum for me to go to?

Thanks,

10e5x