topic Section access strategy for limiting data that users have a one-to-many relationship with (e.g. cost codes) in QlikView

Section access strategy for limiting data that users have a one-to-many relationship with (e.g. cost codes)

Mon, 25 Apr 2011 11:40:15 GMT

Goal:

I want to use section access to restrict data users can see based on the cost codes to which they have permissions.

Problem:

The attached Data Protection PDF, while helpful, provided an example of data reduction only for a one-to-one relationship between user and department (i.e. each user is associated with only one department). In my scenario, each user will associated with many--but not all--cost codes.

Sample Data:

The cost code permissions table has multiple records for each user. It looks something like this:

USER COSTCODE

A 100-01

A 200-01

B 200-01

B 300-01

Questions:

Would this require multiple listings for each user in the Section Access table, one for each cost code? Or is there a different approach I should take?

Thanks for your feedback!

Section access strategy for limiting data that users have a one-to-many relationship with (e.g. cost codes)

Miguel_Angel_Baeyens — Mon, 25 Apr 2011 12:29:49 GMT


redingtonct wrote:Would this require multiple listings for each user in the Section Access table, one for each cost code? Or is there a different approach I should take?

The short answer is: yes, you need to specify every possible COSTCODE value for each USER value.

However, its obviously tedious. So I'd recommend you to create a table, QVD, xls file or something similar with groups of users and memberships, for example, user A member of GROUP1, and user B member of GROUP2, so the section access script looks like

SECTION ACCESS; LOAD ACCESS, USERID, PASSWORD, GROUPFROM SA.QVD (QVD) WHERE 1=1; SECTION APPLICATION; Correspondence: // inline or any other sourceLOAD * INLINE [GROUP, COSTCODEGROUP1, 100-01GROUP1, 200-01GROUP2, 200-01GROUP2, 300-01];

Of course, in the example above, loading INLINE only takes you more time, but if the correspondence is possible, the script will be smaller.

Hope that helps

Section access strategy for limiting data that users have a one-to-many relationship with (e.g. cost codes)

Tue, 26 Apr 2011 02:37:32 GMT

Miguel, thanks for offering the clarification.

In my case, this turns out to be fairly easy. Since the CostCodePermissionsTable includes a record for each cost code that a user has access to, and since the UserLogin is used in that table, I can simply use UserLogin as the GROUP in section access. The resultant code would look like this:


Section Access;
SQL
SELECT UPPER(UserLogin) as NTNAME,
CASE WHEN Role = 'VP' THEN'ADMIN'
ELSE 'USER'
END as ACCESS,
UPPER(UserLogin) as GROUP
FROM UsersTable;
Section Application;
SQL
SELECT UPPER(UserLogin) as GROUP, COSTCODE
FROM CostCodePermissionsTable;