Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Associated Values not showing...

Hi all,

This is my second post, first was answered by someone helpfulSmiley Happy 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?

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Associated Values not showing...

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.


talk is cheap, supply exceeds demand
2 Replies
MVP & Luminary
MVP & Luminary

Re: Associated Values not showing...

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.


talk is cheap, supply exceeds demand
Not applicable

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