Section Access

[stephenfryer]

Morning,

I have developed a dynamic section access for an app, following the Qlik documentation. As far as I can tell, I have followed it correctly, however, section access is not working as expected. 

Within my model, I do have a field labelled SECTIONACCESSKEY, which only contains [ManagerEmployeeID], so I know this matches the section access script below. If anyone could review my script and point me in the right direction, it would be greatly appreciated.

Some key words have been replaced with 'Dummy' for the purpose of posting on this forum.

/*******************************************************************************
Import default manager access
********************************************************************************/
LIB CONNECT TO 'Dummy';

NoConcatenate
Managers:
SQL
SELECT
'USER' AS ACCESS,
lower([ManagerEmailAddress]) AS [USER.EMAIL],
[ManagerEmployeeID] AS SECTIONACCESSKEY
FROM
[DummySchema].[DummyTable]
GROUP BY
[ManagerEmailAddress],
[ManagerEmployeeID]
HAVING
COUNT([EmployeeName]) > 1;

DisConnect;

/*******************************************************************************
Import default manager access level
********************************************************************************/
NoConcatenate
PermissionsList:
Load
ACCESS
,USER.EMAIL
,SECTIONACCESSKEY
Resident
Managers;
Concatenate(PermissionsList)
LOAD * INLINE [
ACCESS, USER.EMAIL, SECTIONACCESSKEY
ADMIN, stephenfryer@Dummy.co.uk, 0002642
];

DROP TABLE
QlikUsers
,Managers
;

Store PermissionsList into [lib://BI Dev:DataFiles/5099 - Colleague KPI/UserPermissions.QVD](qvd);


Drop Table PermissionsList;

/*******************************************************************************
Section Access
********************************************************************************/
Section Access;
Load
ACCESS
,USER.EMAIL
,SECTIONACCESSKEY
From
[lib://BI Dev:DataFiles/5099 - Colleague KPI/UserPermissions.QVD](qvd)
Where
1=1;
Section Application;

[rubenmarin]

Hi, you can try removing "Section Access;" so this table will be loaded as one table, the in design build some simple table to check the values loaded an the relationship between data.

Maybe it has something to do with the leading zeros, in that case try using Text() to load all values as text

[JandreKillianRIC]

Hi @stephenfryer 

First thing I am seeing is that the Email should be forced to Upper case (I see you are doing a lower).

I assume you have a field in your data model that has the fieldname SECTIONACCESSKEY that would do the reduction? 

Perhaps share a image of your data model? Test the above and revert if needed please. 

[Bhushan_Mahajan]

@stephenfryer If user is logging in with his/her ID then there no need to use it as SECTIONACCESSKEY since it usually used for row level access. Your can use ID column as ACCESS,USERID, NAME. It will automatically restrict the data in required format. Refer below script, where I restricted the Data on ID level.

Section Access;
ACCESS:
LOAD
Upper(USERID) as USERID,

Upper("ACCESS") as ACCESS
FROM [Section Access.xlsx];

Concatenate
LOAD
Upper(USERID) as USERID,
Upper("ACCESS") as ACCESS
FROM [RM_Section_Access.xlsx];
Section Application;

[JandreKillianRIC]

Hi @stephenfryer 

If this is for On Prem (Qlik Sense) then you should use USERID, If this is for Qlik Cloud you should use USER.EMAIL. 

Don't know if this is what is causing the misbehavior. 

[stephenfryer]

Thank you all for your suggestions. I will work through them and let you know either why what has/has not worked.

[stephenfryer]

Thanks Rubenmarin.

I removed the section access sections and tested the model on its own, and it worked as intended. I also checked the leading zeros and used the text() function to force them to text, but still no joy.

[stephenfryer]

Hi JandreKillianRIC,

I tried using the UPPER() function on both email columns in the model ahead of it being used in the 'Section Access' section, but it didn't change anything. 

I've attached a picture of the current model. I have uncommented the 'Section Access', so that you can see its relationship to the hierarchy.

[stephenfryer]

The ID I'm using for the SECTIONACCESSKEY is the employeeid, seperate from the USERID. 

I changed the model to use the USERID instead, but its still not limiting the data as intended. When removing the 'Section Access' sections and testing the model manually, it works.

[stephenfryer]

We're using Qlik Cloud. I have tried both USERID and USER.EMAIL, neither of them seem to work.