2 Replies Latest reply: Nov 29, 2012 3:48 AM by guoxiang RSS

    Associated Values not showing...

      Hi all,

      This is my second post, first was answered by someone helpful:) therefore very please with the help from this platform.

      I have this problem over here:

      I have load data from ms access to qlikview, this is how my script looks like:

       

      StaffAccess:

      SQL SELECT `EVENT_D` AS AccessDate,

                `EVENT_D` AS EntryDate,

          `EVENT_T` AS TimeIn,

          `PASS_M` AS StaffName,

          `MESSAGE_X` AS AccessResult

      FROM `TACS_ACCESS_DTL`

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

       

      JOIN(StaffAccess)

       

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

       

      Load

                *,

                Interval(TimeOut-TimeIn, 'hh:mm:ss') as WorkingHours

      resident StaffAccess;

       

      Over at my sheet, when i select AccessDate and the staffName it should give me the assoicated AccessResult and TimeIn and TimeOut. AccessResult and TimeIn is working fine... but TimeOut is not showing any values despite having assoicated values. However when i do a select possible at the TimeOut listbox, it will select the value. (and not eveytime the assocaited values(white in color) are showing on top, why is that so?)

       

      Anyone help pls?

        • Re: Associated Values not showing...
          Gysbert Wassenaar

          The two tables are joined on the common fields. Those are AccessDate,StaffName and AccessResult. That is probably not what you want. Because AccessResult is different in the two tables, no records are matched. So what happens is this:

           

          Entry





          AccessDateEntryDateTimeInStaffnameAccessResult

          01-01-201201-01-20128:00JohnLegal Access

          02-01-201202-01-20129:00JackLegal Access








          Exit





          AccessDateExitDateTimeOutStaffnameAccessResult

          01-01-201201-01-20129:00JohnLegal Exit

          02-01-201202-01-201210:00JackLegal Exit








          Join result:





          AccessDateEntryDateTimeInStaffnameAccessResultExitDateTimeOut
          01-01-201201-01-20128:00JohnLegal Access--
          02-01-201202-01-20129:00JackLegal Access--
          01-01-2012--JohnLegal Exit01-01-20129:00
          02-01-2012--JackLegal Exit02-01-201210:00

           

          The solution is to make sure the tables are joined on the right key. In your case I think you want to match EntryDate to ExitDate. It would help if an access (event) has a unique identifier like EventID. Then you could match on only that key. You'll have to make sure that any fields that shouldn't be used to join on have different names in the two tables.

            • Re: Associated Values not showing...

              Hi Gysbert,

              Thanks for the reply. Yes your are right and i solved that by redoing the Seelct statement with a join.(with the help of Joahannes for QlikView).

              Since we are at it, i hope u can help me with my other problems... the more urgent one is getting the employees who work more than X amount of hours on Y date.

              X and Y are to be selected by the user...

              How should i go about doing that? i am guessing input box and set the variable. But i only started using QlikView 2 days ago, and the tutorial on qlikview is too basic(just modify the numbers).

               

              Regards,

              10e5x