QlikView App Dev

[#1]

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?

[Gysbert_Wassenaar · Accepted solution]

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
AccessDate	EntryDate	TimeIn	Staffname	AccessResult
01-01-2012	01-01-2012	8:00	John	Legal Access
02-01-2012	02-01-2012	9:00	Jack	Legal Access
Exit
AccessDate	ExitDate	TimeOut	Staffname	AccessResult
01-01-2012	01-01-2012	9:00	John	Legal Exit
02-01-2012	02-01-2012	10:00	Jack	Legal Exit
Join result:
AccessDate	EntryDate	TimeIn	Staffname	AccessResult	ExitDate	TimeOut
01-01-2012	01-01-2012	8:00	John	Legal Access	-	-
02-01-2012	02-01-2012	9:00	Jack	Legal Access	-	-
01-01-2012	-	-	John	Legal Exit	01-01-2012	9:00
02-01-2012	-	-	Jack	Legal Exit	02-01-2012	10: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.

[#3]

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