Section Access to omit fields based on management structure

[LReeve]

Hi All,

Am struggling to get my head round a Section Access problem I have.

I want the app to show every field for everyone except from 2 fields (FieldA and FieldB). What I want to achieve is if USERA does not manage USERB then Omit both fields. That way the user can only see those comments for someone who is directly in their team. I have a document which says USERAID managers USERBID but I have no idea how to get this in Qlik?

I currently have a basic Section Access configured which excludes those fields from certain IDs but it does not cover the example above.

Thanks

Would appreciate any help!

[LReeve]

On reflection I'm aware a little bit more info is needed.

An example table structure would be:

Leader	Staff	FTE	Salary
USERA	USER1	1	15000
USERA	USER2	1	18000
USERA	USER3	0.5	12000
USERB	USER4	1	26000
USERB	USER5	0.8	23000

Both USERA and USERB need to see all FTE information but should only be able to see the Salary info where they are listed as the Leader. Their Qlik login matches the Leader field.

Thanks

[marcus_sommer]

To get this you will need 2 salary fields like SaleryA and SaleryB and then you could with OMIT control their visiblity to the users.

- Marcus

[LReeve]

Hi Marcus,

I have around 50 Leaders to apply this to so creating it using this method does not seem feasible - unless there is a way to loop through and create and populate the column for every leader?

Thanks

[marcus_sommer · Accepted solution]

The problem is that you want to display all data from some fields and at the same time reducing the records from other fields and this is conflicting.

Some years ago we had had a similar case and we developed a very complex solution with huge loops to create the section access tables, which was also loaded twice and needed some variables and triggers and also the requirement that a user couldn't change objects or create own ones - it worked but it was a maintenance nightmare ...

Therefore we replaced it after some time with two specialized applications which both fulfill their requirements much better as our first matching of too different applications. This might be the easiest way ...

If this is not possible or preferable you should give this a try: Mask or de-identify data for certain users using Section Access.

- Marcus

[LReeve]

Thanks for sharing the link Marcus.

I've had a look and seems to match in principal what I need - but otherwise I think I'll need to re-evaluate the application design.

Edit: Using the link I managed to achieve exactly what I needed. Thanks for sharing!

Thanks

[Lauri]

I think I have a solution for you, where you declare a field in Section Access, then use that field in Section Application to limit access.

The setup might be something like:

Section Access;

tblAccess:

Load ACCESS, USERID, UPPER(USERID) as LEADER, OMIT

From [your user list];

Section Application;

tblReduction:

LOAD LEADER, STAFF_ANYONE, STAFF_LEADERONLY;

SQL SELECT u.USERID AS LEADER, d.Staff as STAFF_ANYONE, case when u.USERID = d.Leader then d.Staff else NULL END as STAFF_LEADERONLY

from [your user list] u

cross join (select distinct Leader, Staff from [your leader-staff table]) d;

tblSalaryData:

Load Staff as STAFF_LEADERONLY, Salary

From [your salary data];

tblFTEdata:

Load Staff as STAFF_ANYONE, FTE

From [your salary data];

The result of all this is that each LEADER can see anyone's FTE data, but only his/her employees' Salary data. It's all in the way the tables link together, starting with the LEADER field in the hidden table in section access. That links to LEADER in tblReduction, and then the two STAFF fields link from there to the two data tables.

My underlying data reside in SQL Server, so I use CROSS JOIN to get the cartesian product of the two tables (so that every Leader can see every employee's FTE data). In Qlik, you can achieve the same with OUTER JOIN.

[marcus_sommer]

If I understand your suggestion right does it mean to develop two independent datamodels within the application because you will for example need different fields for the EmployeeID and also to link these data to further dimension-tables like a calendar. If you connect them in any way you will apply the section access data-reduction again and/or creating some synthetic key or circular loops.

In some cases two datamodels might be a practically way but for many scenarios it will be rather not suitable.

- Marcus

[Lauri]

I don't know if this counts as one or two data models... but I haven't had any circular loops with this setup. You are correct, there are two fields for the Employee -- the one that links every employee to every leader, and the one that links only team members to the leader.

[LReeve]

Thanks Lauri,

Will look at this in my next app.

Thanks