How to filter records in Section Access?

[ledi12]

Hi,

I have section access script where I assign access for user based on general information of tenant or region (users can access table's data where tenant equals 1 or 2).

I have also another field "code". Each of the tenant can have numerous codes like 111, 222, 333 etc.

I would like to filter out all rows where code = 333, and grant users access to these. All other codes should be restricted to view.

How can I implement filtering in section access?

[marcus_sommer]

It could be done by combining both fields like:

tenant & ' | ' code

within the section access table and the related data-model table by defining the values appropriate, means like:

1 | 333
2 | 333

- Marcus

[AndyC]

Something like this would be the kind of solution you would expect to work...

Section Access;
[Security]:
LOAD * inline [
ACCESS, USERID, GROUP, TENANT_FILTER, CODE_FILTER
USER, AndyC, *, 1, ALL
USER, AndyC, *, 2, 333
];

Section Application;
Star is *;

[Tenant Filter]:
LOAD * Inline [
TENANT_FILTER, TENANT
ALL, 1
ALL, 2
1, 1
2, 2
];

[Code Filter]:
LOAD * Inline [
CODE_FILTER, CODE
ALL, 111
ALL, 222
ALL, 333
111, 111
222, 222
333, 333
];

[Data Table]:
Load * Inline [
ITEM, TENANT, CODE
One, 1, 111
Two, 1, 222
Three, 2, 111
Four, 1, 333
Five, 2, 333
Six, 2, 222,
Seven, 1, 111
];

BUT the problem is that the two filters are effectively an OR on the data. You want an AND filter so you will have to combine the two columns into one value in the data and put the combinations you want in a single section access column

[ledi12]

Thanks guys for your response 🙂

I have also another problem which is related. I'm accessing some of the access based on external table comming from aws. There two relevant columns "code", "security_code", "center_num".  Each user have center_num assigned but only one code contains value. If code is like 333, then security_code is null. It works also on opposite.

I wonder how can move this logic to my section access. Here is my current code:

SUB Load_SECTION_ACCESS

  SET vL.SourceTableName = 'DIM_QS_USER_ACCESS';

  Star is *;

  SECTION Access;

  SECTION_ACCESS_TABLE:
  LOAD DISTINCT
    'USER' AS ACCESS,
    "USER_ID" AS USERID,
    "CENTER_NUM" AS %CENTER_NUM,
    "SECURITY_CODE" AS %SECURITY_CODE,
    "CODE" AS %CODE,
    "OMIT_COLUMN" AS OMIT
  FROM
    [$(vL.QVDCleansingPath)/$(vL.SourceTableName).qvd] (QVD);
  CONCATENATE(SECTION_ACCESS_TABLE)
  LOAD
    'USER' AS ACCESS,
    'TECHNICAL_USER' AS USERID,
    "CENTER_NUM" AS %CENTER_NUM,
    "SECURITY_CODE" AS %SECURITY_CODE,
    "CODE" AS %CODE,
    'x' AS OMIT
  RESIDENT FCT_MAIN;
  CONCATENATE(SECTION_ACCESS_TABLE)
  LOAD
    'USER' AS ACCESS,
    'TECHNICAL_USER' AS USERID,
    "CENTER_NUM" AS %CENTER_NUM,
    'x' AS OMIT
  RESIDENT DIM_COST_CENTER_OWNERS;
  CONCATENATE(SECTION_ACCESS_TABLE)

  LOAD * INLINE [
  ACCESS, USERID, %INTERNAL_COST_CENTER_NUM, OMIT
  ADMIN, INTERNAL\SA_SCHEDULER, *,
  ];

  SECTION Application;

ENDSUB

I want to retrieve all records to which user can have access. I thought that qlik wouldn't try to join null values but apparently it behaves like either code and security_code have values so it tries to fetch it. Eventually it fetches nothing and I receive access denied. 

How can refactor it to fetch security_code records if security_code is not null and the same for code?