Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Problematic issue...Help needed

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

1 Solution

Accepted Solutions
jsn
Honored Contributor

Re: Problematic issue...Help needed

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;

6 Replies
jsn
Honored Contributor

Re: Problematic issue...Help needed

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;

Highlighted
Not applicable

Re: Problematic issue...Help needed

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? ConfusedSmiley Sad

Appreciated,

10e5x

Not applicable

Re: Problematic issue...Help needed

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%';

jsn
Honored Contributor

Re: Problematic issue...Help needed

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.

Not applicable

Re: Problematic issue...Help needed

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

Not applicable

Re: Problematic issue...Help needed

Hi Johannes,

Your solutions work!!! i got the new field working hours. But got NO VALUESmiley Sad (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

Community Browser