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

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

Fri, 20 Nov 2020 18:02:30 GMT

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:

ID	EntryDT	ExitDT	StaffName	WorkingHrs
1	Unknown	21/11/12 23:01:00	John	Unknown
2	21/11/12 23:10:05	22/11/12 07:20:00	Peter	8hrs10mins
3	22/11/12 07:19:00	22/11/12 17:00:00	John	9hrs41mins
4	22/11/12 07:30:00	22/11/12 19:01:00	Joe	11hrs31min
5	22/11/12 08:00:00	22/11/12 19:30:00	Mike	11hrs30min
6	22/11/12 22:00:00	23/11/12 08:00:00	Mary	10hrs
7	23/11/12 07:01:00	Unknown	John	Unknown
8	23/11/12 09:00:00	Unknow	Peter	Unknown

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

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

hic — Mon, 03 Dec 2012 15:02:30 GMT

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

HIC

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

Mon, 03 Dec 2012 15:18:48 GMT

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

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

hic — Mon, 03 Dec 2012 15:34:17 GMT

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

HIC

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

Tue, 04 Dec 2012 00:48:20 GMT

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