Restrict Users into a QVD file or QVW file to see data related to them? Please Help me in correcting below query

[#1]

Hi,

Could you please guide me in correcting below query:

Section Access;  /* Start section access script */

LOAD * INLINE

[

    ACCESS, USERID, PASSWORD, BUSINESSENTITYID

    ADMIN,  ADMIN,  ADMIN

    USER,   USERA,  U1,       1

    USER,   USERB,  U2,       2

];

Section Application; /* End section access script */

EMP_DEP:

LOAD * INLINE

[

SELECT A.GroupName,C.BUSINESSENTITYID

FROM HumanResources.Department A

INNER JOIN HumanResources.EmployeeDepartmentHistory B ON A.DepartmentID=B.DepartmentID

INNER JOIN HumanResources.Employee C ON B.BusinessEntityID = C.BusinessEntityID

INNER JOIN HumanResources.EmployeePayHistory D ON C.BusinessEntityID = D.BusinessEntityID

];

It is not giving correct output

How can we restrict Users into a QVD file or QVW file to see data related to them?

Link: SQL Tips: Section Access (Qlikview)

[#2 · Accepted solution]

Hi Guys,

Below is corrected code:

Section Access;  /* Start section access script */

LOAD * INLINE

[

    ACCESS, USERID, PASSWORD, BUSINESSENTITYID

    ADMIN,  ADMIN,  ADMIN,   *

    USER,   USERA,  U1,      1

    USER,   USERB,  U2,       2

    USER,   USERC,  U3,       1

    USER,   USERC,  U3,       2

];

Section Application; /* End section access script */

SELECT A.GroupName,C.BUSINESSENTITYID

FROM HumanResources.Department A

INNER JOIN HumanResources.EmployeeDepartmentHistory B ON A.DepartmentID=B.DepartmentID

INNER JOIN HumanResources.Employee C ON B.BusinessEntityID = C.BusinessEntityID

INNER JOIN HumanResources.EmployeePayHistory D ON C.BusinessEntityID = D.BusinessEntityID;

Here Admin can see all BUSINESSENTITYID but USERA can see only BUSINESSENTITYID=1 and USERB can see only BUSINESSENTITYID=2 and USERC can see both BUSINESSENTITYID 1 & 2.

Note: After writing Above Script save It and then Select or Check Option "Initial data reduction based on section access" and "Strict Exclusion" in Settings-->Document Properties-->Opening and then Reload.

After doing above setting Close the ClikView (Not only .QVW File) application and again reopen the particular QVW file and Enter User and Password and will be able to see data only related to that user.