Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Jonathan_Alm
Partner - Creator
Partner - Creator

Section access two fields

Hi,
We are currently implementing section access which requires two fields that users can belong to.

The setup looks like this:

Section Access table 1

NTNAME ACCESS %AREAID
DOMAIN/USER1 USER GERMANY
DOMAIN/USER2 USER NORWAY
DOMAIN/USER3 USER SWITZERLAND

 

Section Access table 2

NTNAME ACCESS %PRODUCTID
DOMAIN/USER7 USER CHAIR
DOMAIN/USER8 USER TABLE
DOMAIN/USER9 USER APPLE

 

A user can only occur in one table, either you have access to see  one country but for products or one products but for all countries.

So in the example above, USER1 should see sales for all products belonging to Germany  and USER7 should see sales for all countries but only for product CHAIR

The data model consist of two dimensional tables: PRODUCT and AREA that contains the corresponding keys.

We are getting access denied when we try to create a concatenated section access table that looks like this:

NTNAME ACCESS %AREAID %PRODUCTID
DOMAIN/USER1 USER GERMANY  
DOMAIN/USER7

USER

  CHAIR

 

We have tried to put * when there are nulls in a "key" and also <ALL> or <ANY> but with no success.

Anyone have an idea of how to solve this issue with section access?

Kind regards, Jonathan

Labels (2)
3 Solutions

Accepted Solutions
Chanty4u
MVP
MVP

Hi,

That should work. May be some other reason .

  1. Try simplifying your script to isolate the issue. For example, you could try removing the CONCATENATE statement and just loading data from one source file to see if that resolves the issue.

  2. Check your Qlik Sense settings and make sure that there are no security settings that could be causing issues. For example, there may be a setting that restricts access to certain fields or data based on user roles

View solution in original post

marcus_sommer

Never tried a section access logic like this one. Therefore just a few hints which may impact the behaviour:

  • is the wildcard specified by something like: star is *; ?
  • the logic to connect the section access table with two dim-tables may lead to a circular reference (I don't know if the section access tables remains within the data-model after the opening respectively if these associations would be further active - and therefore if it has a relevant impact) - so an association move to the fact-table might be the better choice (yes, it would create a synthetic key but this will work within the most scenarios)
  • there are syntax-mistakes within the below shown script (missing [] which should normally lead to  a load-error)
  • a concatenation of non-equally fields results mostly in NULL (there are exceptions, for example by the use of error-variable) and therefore the following isnull() check may fail - more robust is usually to query against: if(len(trim(Field)), Field, '*')
  • further the wildcard considers only real values - NULL within the section access as well as within the data should be avoided

I think I would at first manipulate (maybe with another concatenate or by adjusting the if-query) with the section access data by adding a valid product and/or area by the users which shouldn't have ones - does it work now? Further helpful is also to comment the section access statement to load the table as real and visible data-table into the data-model because now you could add a table-box in the UI and  a few list-boxes to see which data are really there and is the relationship like expected to ensure the right data are there.

View solution in original post

Jonathan_Alm
Partner - Creator
Partner - Creator
Author

Hi,
we have managed to solve this. You were both correct that it should work and it finally did.

It was a combination of error in syntax and some trial and error.

 

The final solution was to have one row per user  in each section access file. So USER1 was in both access files with a * to get all access (see below).

The section were we tried to mimic this with the "if(isnull(...." and enter a * when null or just an empty column never worked. 

Final solution:

Section Access;
ACCESS:
LOAD
NTNAME,
"ACCESS",
UPPER(PRODUKTOMRÅDE) as %PRODUCTID
FROM [lib://Container_SharedFolders_Prod/1.QVD/1.Extract/SectionAccess/SectionAccess_PRODUCTID.qvd]
(qvd);
Concatenate
LOAD
NTNAME,
"ACCESS",
UPPER(AFFÄRSOMRÅDE) as %AREAID
FROM [lib://Container_SharedFolders_Prod/1.QVD/1.Extract/SectionAccess/SectionAccess_Affärsområde.qvd]
(qvd);

section application;

 

And the files read from:

Section Access table 1

NTNAME ACCESS %AREAID
DOMAIN/USER1 USER GERMANY
DOMAIN/USER2 USER NORWAY
DOMAIN/USER3 USER SWITZERLAND
DOMAIN/USER7 USER *
DOMAIN/USER8 USER *
DOMAIN/USER9 USER *

 

Section Access table 2

NTNAME ACCESS %PRODUCTID
DOMAIN/USER7 USER CHAIR
DOMAIN/USER8 USER TABLE
DOMAIN/USER9 USER APPLE
DOMAIN/USER1 USER *
DOMAIN/USER2 USER *

 

Thanks for your input and help

Kind regards, 

View solution in original post

8 Replies
Chanty4u
MVP
MVP

 

you can use a combination of wildcard values and the NULL keyword to represent the absence of a value in a field. Here's an example of a concatenated section access table that should work for your  case 

NTNAME, ACCESS, %AREAID, %PRODUCTID

DOMAIN/USER1, USER, GERMANY, *

DOMAIN/USER2, USER, NORWAY, *

DOMAIN/USER3, USER, SWITZERLAND, *

DOMAIN/USER7, USER, *, CHAIR

DOMAIN/USER8, USER, *, TABLE

DOMAIN/USER9, USER, *,

APPLE

Jonathan_Alm
Partner - Creator
Partner - Creator
Author

Hi,

we have tried with a * but that only works for users with access "ADMIN", not with users with ACCESS "USER". 

So when we try to add a user with access: DOMAIN/USER1, USER, GERMANY, * the users gets denied.

 

This is the complete script:

Section Access;
ACCESS:
LOAD
    NTNAME,
    "ACCESS",
    UPPER(AREAID) as %AREAID
FROM [lib://Container_SharedFolders_Prod/1.QVD/1.Extract/SectionAccess/SectionAccess_AREA.qvd]
(qvd);
Concatenate
LOAD
    NTNAME,
    "ACCESS",
    UPPER(PRODUCTID) as %PRODUCTID
FROM [lib://Container_SharedFolders_Prod/1.QVD/1.Extract/SectionAccess/SectionAccess_PRODUCT.qvd]
(qvd);

ACCESS2:
NoConcatenate
load
NTNAME,
    ACCESS,
    if(IsNull([%AREAID), '*', [%AREAID]) as [%AREAID],
    if(IsNull([%PRODUCTID), '*', [%PRODUCTID]) as [%PRODUCTID]
resident ACCESS;

drop table ACCESS;




Kind regards, Jonathan

Chanty4u
MVP
MVP

Hi, you can try this

Section Access;
ACCESS:
LOAD
NTNAME,
"ACCESS",
UPPER(AREAID) as %AREAID
FROM [lib://Container_SharedFolders_Prod/1.QVD/1.Extract/SectionAccess/SectionAccess_AREA.qvd]
(qvd);
Concatenate
LOAD
NTNAME,
"ACCESS",
UPPER(PRODUCTID) as %PRODUCTID
FROM [lib://Container_SharedFolders_Prod/1.QVD/1.Extract/SectionAccess/SectionAccess_PRODUCT.qvd]
(qvd);

ACCESS2:
NoConcatenate
load
NTNAME,
ACCESS,
if(IsNull([%AREAID]), '', [%AREAID]) as [%AREAID],
if(IsNull([%PRODUCTID]), '', [%PRODUCTID]) as [%PRODUCTID]
resident ACCESS;

drop table ACCESS;

Jonathan_Alm
Partner - Creator
Partner - Creator
Author

Hi,

Still getting access denied.

This is the result from Section Access (USER1 is me):

NTNAME ACCESS %AREAID %PRODUCTID
DOMAIN/USER1 USER GERMANY  

 

Works if I enter a PRODUCTID but not with emtpy '' or '*' 

Kind regards, Jonathan

Chanty4u
MVP
MVP

Hi,

That should work. May be some other reason .

  1. Try simplifying your script to isolate the issue. For example, you could try removing the CONCATENATE statement and just loading data from one source file to see if that resolves the issue.

  2. Check your Qlik Sense settings and make sure that there are no security settings that could be causing issues. For example, there may be a setting that restricts access to certain fields or data based on user roles

marcus_sommer

Never tried a section access logic like this one. Therefore just a few hints which may impact the behaviour:

  • is the wildcard specified by something like: star is *; ?
  • the logic to connect the section access table with two dim-tables may lead to a circular reference (I don't know if the section access tables remains within the data-model after the opening respectively if these associations would be further active - and therefore if it has a relevant impact) - so an association move to the fact-table might be the better choice (yes, it would create a synthetic key but this will work within the most scenarios)
  • there are syntax-mistakes within the below shown script (missing [] which should normally lead to  a load-error)
  • a concatenation of non-equally fields results mostly in NULL (there are exceptions, for example by the use of error-variable) and therefore the following isnull() check may fail - more robust is usually to query against: if(len(trim(Field)), Field, '*')
  • further the wildcard considers only real values - NULL within the section access as well as within the data should be avoided

I think I would at first manipulate (maybe with another concatenate or by adjusting the if-query) with the section access data by adding a valid product and/or area by the users which shouldn't have ones - does it work now? Further helpful is also to comment the section access statement to load the table as real and visible data-table into the data-model because now you could add a table-box in the UI and  a few list-boxes to see which data are really there and is the relationship like expected to ensure the right data are there.

Jonathan_Alm
Partner - Creator
Partner - Creator
Author

Hi,
we have managed to solve this. You were both correct that it should work and it finally did.

It was a combination of error in syntax and some trial and error.

 

The final solution was to have one row per user  in each section access file. So USER1 was in both access files with a * to get all access (see below).

The section were we tried to mimic this with the "if(isnull(...." and enter a * when null or just an empty column never worked. 

Final solution:

Section Access;
ACCESS:
LOAD
NTNAME,
"ACCESS",
UPPER(PRODUKTOMRÅDE) as %PRODUCTID
FROM [lib://Container_SharedFolders_Prod/1.QVD/1.Extract/SectionAccess/SectionAccess_PRODUCTID.qvd]
(qvd);
Concatenate
LOAD
NTNAME,
"ACCESS",
UPPER(AFFÄRSOMRÅDE) as %AREAID
FROM [lib://Container_SharedFolders_Prod/1.QVD/1.Extract/SectionAccess/SectionAccess_Affärsområde.qvd]
(qvd);

section application;

 

And the files read from:

Section Access table 1

NTNAME ACCESS %AREAID
DOMAIN/USER1 USER GERMANY
DOMAIN/USER2 USER NORWAY
DOMAIN/USER3 USER SWITZERLAND
DOMAIN/USER7 USER *
DOMAIN/USER8 USER *
DOMAIN/USER9 USER *

 

Section Access table 2

NTNAME ACCESS %PRODUCTID
DOMAIN/USER7 USER CHAIR
DOMAIN/USER8 USER TABLE
DOMAIN/USER9 USER APPLE
DOMAIN/USER1 USER *
DOMAIN/USER2 USER *

 

Thanks for your input and help

Kind regards, 

techguy100
Contributor II
Contributor II

Based on the information provided, it seems like the issue you're facing with the concatenated section access table is related to the way that null values are being handled.

In order to resolve this issue, you may want to consider using the "NullAsValue" statement in your section access script. This statement allows you to specify a value to use in place of null values, which can help ensure that your section access table is properly concatenated.

For example, you could add the following line to your section access script:

NullAsValue <ANY>;

This statement tells QlikView to replace any null values with the string "<ANY>". You can adjust the value used in place of nulls to match the values used in your section access tables, such as "<ALL>" or "*".

Another approach you could try is to split your concatenated section access table back into two separate tables, one for %AREAID and one for %PRODUCTID. You could then use the "Concatenate" keyword in your LOAD statements to combine the two tables into a single table in your data model.

For example:

Section Access table 1:
LOAD NTNAME, ACCESS, %AREAID
FROM [lib://SectionAccessTable1.qvd];

Section Access table 2:
LOAD NTNAME, ACCESS, %PRODUCTID
FROM [lib://SectionAccessTable2.qvd];

Concatenate (Section Access):
LOAD NTNAME, ACCESS, %AREAID, %PRODUCTID
FROM [lib://SectionAccessTable1.qvd]
LEFT JOIN [lib://SectionAccessTable2.qvd]
ON NTNAME;

By splitting the tables and using the "Concatenate" keyword, you can ensure that null values are properly handled and that your section access rules are applied correctly.

I hope this helps you resolve the issue with your section access implementation. If you continue to experience issues, you may want to consult the QlikView documentation or reach out to the QlikView community for additional assistance.

 

Regards,

Rachel Gomez