Section access two fields

[Jonathan_Alm]

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

[Chanty4u]

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]

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]

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]

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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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]

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