topic Re: Problematic issue...Help needed in QlikView

Problematic issue...Help needed

Wed, 28 Nov 2012 08:39:51 GMT

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

Re: Problematic issue...Help needed

Anonymous — Wed, 28 Nov 2012 08:58:57 GMT

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;

Re: Problematic issue...Help needed

Wed, 28 Nov 2012 09:05:33 GMT

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

Re: Problematic issue...Help needed

Wed, 28 Nov 2012 09:37:20 GMT

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

Re: Problematic issue...Help needed

Anonymous — Wed, 28 Nov 2012 09:39:27 GMT

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.

Re: Problematic issue...Help needed

Wed, 28 Nov 2012 09:49:21 GMT

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

Re: Problematic issue...Help needed

Thu, 29 Nov 2012 01:06:15 GMT

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