6 Replies Latest reply: Nov 28, 2012 8:23 PM by guoxiang RSS

    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,


        • Re: Problematic issue...Help needed
          Johannes Sunden

          Here's one script you can run to test:



          Load * Inline [


















                    EventTime as InTime,


          Resident T1

          where EventType='In';



          join (T2)



                    EventTime as OutTime,


          Resident T1

          where EventType='Out';


          Drop table T1;




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

          resident T2;


          Drop table T2;

            • 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? Confused:(




              • 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];


                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];


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