Section Application: security on field that may not exist

[Or]

Dear all,

I am trying to set up QlikView on some HR data, with the following stipulations for security:

Each line from the time clock has data for the department the employee signed into.

Each department manager should be able to view all data for their department.

This has not been a problem - a simple, one-field Section Application achieved this with no problem. However, I now have a new stipulation:

People from HR should be able to see data for all employees, including those who have not reported any actual work.

In this situation, HR would still want to see the employee's general details (e.g. name, telephone, address, etc). However, because I am filtering based on the department line from the *actual work report*, any employee not appearing in this table (i.e. no actual work) is simply filtered out of the QVW entirely.

Sample data:

Date	Employee	Hours	DEPARTMENT
1/1/2012	1	8	IT
1/1/2012	2	8	HR
2/2/2012	1	9	IT
2/2/2012	3	7	SALES

Employee	Name	Phone
1	Joe Smith	123
2	John Doe	456
3	James White	789
4	Jennifer Smith	555-555-5555

Section Application;

LOAD * INLINE [

USER, DEPARTMENT

ITMANAGER, IT

SALESMANAGER,SALES

HRManager, *

];

In this context, nobody would be able to view the details for Employee 4 (Jennifer Smith), because there is no department data associated with this employee.

I would like to set things up so HRManager could still see data for this employee. Moving the department data to the employee table is not feasible - it needs to be the department where said employee actually worked, not the one they are normally assigned to. I can't set up a basic two-field set of permissions, because it would create a loop.

Any ideas would be welcome.

Thanks in advance.

[swuehl · Accepted solution]

What about concatenating a list of all employees to your fact table, only with fields Employee and DEPARTEMENT (constant value 'HR') filled.

Section Application;

Hours:

LOAD Recno() as RecID,

     Date,

     Employee,

     Hours,

     DEPARTMENT

FROM

[http://community.qlik.com/thread/57559?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

Employees:

LOAD Employee,

     Name,

     Phone

FROM

[http://community.qlik.com/thread/57559?tstart=0]

(html, codepage is 1252, embedded labels, table is @2);

Concatenate (Hours) LOAD Employee, 'HR' as DEPARTMENT Resident Employees;

Reducing to DEPARTMENT value HR returns only hours worked for HR, but still all Employees, reducing to some other DEPARTMENT show only hours worked and related Employees for that department.

Hope this helps,

Stefan