Problematic issue...Help needed

[#1]

Hi all QlikViewers,

I am new to QlikView. Completed the beginner tutorial, find QlikView very interesting. After which, i am tasked by my supervisor to analyse staff performance. I have created a new document with the neccessary fields added in through SQL Select of my .mdf table, however i am stucked at deriving the no. of working hours.

This is how my .mdf table looks like(and i do not have db rights to alter this table):

EventDate | EventTime | EventType | StaffName

28/11/12      07:33:57     In               AB

28/11/12      08:34:57     In               BC

28/11/12      17:36:57    Out             BC

29/11/12      17:34:57    Out             AB

29/11/12      07:34:00     In               BC

29/11/12      17:39:57    Out             BC

30/11/12      07:34:00     In               AB

30/11/12      09:34:00     In               BC

30/11/12      17:38:57     Out            AB

30/11/12      17:20:57     Out            BC

Scenario i want to achieve:

Now when i choose the date, and the staffname, i will be able to get the assoicated timeIn and timeOut through the association rules.

However, I want to allow the user to choose any date with a combination with a staff name then it will generate the no.of working hours out.

Is this even possible in QlikView?

I guess it should be done at the Edit Script right? But how do i capture the value selected by user?

I just want to display the no.of working hours of the staff.

With hope,

10e5x

[Anonymous · Accepted solution]

Here's one script you can run to test:

T1:

Load * Inline [

EventDate,EventTime,EventType,StaffName

28/11/12,07:33:57,In,AB

28/11/12,08:34:57,In,BC

28/11/12,17:36:57,Out,BC

28/11/12,17:34:57,Out,AB

29/11/12,07:34:00,In,BC

29/11/12,17:39:57,Out,BC

30/11/12,07:34:00,In,AB

30/11/12,09:34:00,In,BC

30/11/12,17:38:57,Out,AB

30/11/12,17:20:57,Out,BC

];

T2:

Load

          EventDate,

          EventTime as InTime,

          StaffName

Resident T1

where EventType='In';

join (T2)

Load

          EventDate,

          EventTime as OutTime,

          StaffName

Resident T1

where EventType='Out';

Drop table T1;

Load

          *,

          Interval(OutTime-InTime, 'hh:mm:ss') as TimeAtWork

resident T2;

Drop table T2;

[#3]

Thanks for the reply johannes. Maybe i am too new to QlikView or too noob. I do not uds what your script are doing...Give me some time to digest, especially the load * Inline part as i have been only using SQL Select. But i am guessing you are loading data i already have inside? Btw i have 6000plus records, how can i load it in using inline? Confused:(

Appreciated,

10e5x

[#4]

Johannes Suden, it gives me all sort of error. i give u my original script.

ODBC CONNECT TO [MS Access Database;DBQ=D:\Intern\ KWEK GUO XIANG\sample.mdb];

Entry:

SQL SELECT `EVENT_D` AS AccessDate,

          `EVENT_D` AS EntryDate,

          FORMAT(CDate(EVENT_D),'DD/MM/YYYY') AS CDate,

    `EVENT_T` AS TimeIn,

    `PASS_M` AS StaffName,

    `MESSAGE_X` AS AccessResult

FROM `TACS_ACCESS_DTL`

WHERE `MESSAGE_X` Like '%Legal Access%';

ODBC CONNECT TO [MS Access Database;DBQ=D:\Intern\ KWEK GUO XIANG\sample.mdb];

Exit:

SQL SELECT `EVENT_D` AS AccessDate,

          `EVENT_D` AS ExitDate,

    `EVENT_T` AS TimeOut,

    `PASS_M` AS StaffName,

    `MESSAGE_X` AS AccessResult

FROM `TACS_ACCESS_DTL`

WHERE `MESSAGE_X` Like '%Legal Exit%';

[Anonymous]

I'm saying:

1. Create a new document

2. Copy and paste the script I wrote

3. Reload document and you can see an example of transforming the data

You can apply similar logic to your example if you want.

[#6]

Thanks Johannes, i run it and it is great. However now to apply to my context kinda lost....I try first.

[#7]

Hi Johannes,

Your solutions work!!! i got the new field working hours. But got NO VALUE:( (dk why)

Mind explaing how the load residents work? Want to learn more. Althogh your solutions work but my app do not. I guess the reason is that my timeOut value wouldnt display the assoicated out... i do not know why. Its all greyed even though there are assoicated values. If i do a right click and do a select possible, the value will be selected but not shown... any idea why?

Thanks,

10e5x