Qlik Community

QlikView Documents

Documents for QlikView related information.

Complex Authorization - How to create authorization bridge

vitaliichupryna
Contributor

Complex Authorization - How to create authorization bridge

Hi Guys,

In this article I would like to share my experience with section access. I didn’t find articles in the community with similar solution and I hope it will be helpful for people that meet with complex authorization face to face first time.

First of all  in my opinion the most useful articles about complex section access is  Basics for complex authorization and Data Reduction Using Multiple Fields written by Henric Cronström.

As we can see in the articles in the simplest case, the authorization table can have only two columns: USER and reducing field (Region, etc.), but usually we have two or three columns for data reducing.

In case of two columns we have four combinations of %AuthID and need make our load four times:

    Load Region &'|'& Product as AuthID, Region &'|'& Product as %AuthID  From OrderDetails ;
    Load Region &'|'& Product as AuthID, Region &'|'&'<ANY>' as %AuthID  From OrderDetails ;
    Load Region &'|'& Product as AuthID, '<ANY>'&'|'& Product as %AuthID  From OrderDetails ;
    Load Region &'|'& Product as AuthID, '<ANY>'&'|'&'<ANY>' as %AuthID  From OrderDetails ;

In case of three reducing fields, we have eight combinations.

Number of combinations can be calculated by following formula:

C = 2n

because every field can be in two states: <ANY> or Value

In my case I had 8 columns for authorization and 256 combinations, I was upset when understood that I should create 256 tables with different combinations manually to load my Authorization Bridge table correctly. I started looking for a solution, below you can see script that solve the problem. In the example I use only four field because I want show the general idea.

Steps:

1. Create table with one field and two values

Temp:
LOAD * Inline [
      Field
      Value
      <ANY>
]
;

Create table with Field combinations
TmpCombinations:
LOAD Field AS Field1 Resident Temp;
join LOAD Field AS Field2 Resident Temp;
join LOAD Field AS Field3 Resident Temp;
join LOAD Field AS Field4 Resident Temp;
DROP TABLE Temp;

 

TmpCombinations table:

Field1

Field2

Field3

Field4

<ANY>

<ANY>

<ANY>

<ANY>

<ANY>

<ANY>

<ANY>

Value

<ANY>

<ANY>

Value

<ANY>

<ANY>

<ANY>

Value

Value

<ANY>

Value

<ANY>

<ANY>

<ANY>

Value

<ANY>

Value

<ANY>

Value

Value

<ANY>

<ANY>

Value

Value

Value

Value

<ANY>

<ANY>

<ANY>

Value

<ANY>

<ANY>

Value

Value

<ANY>

Value

<ANY>

Value

<ANY>

Value

Value

Value

Value

<ANY>

<ANY>

Value

Value

<ANY>

Value

Value

Value

Value

<ANY>

Value

Value

Value

Value

2.      Create table with real field names

NoConcatenate
Combinations:
LOAD
if(Field1='Value', '[Region]',chr(39)&Field1&chr(39))      AS Field1,
if(Field2='Value', '[Product]',chr(39)&Field2&chr(39))     AS Field2,
if(Field3='Value', '[SubRegion]' ,chr(39)&Field3&chr(39))  AS Field3,
if(Field4='Value', '[SubProduct]',chr(39)&Field4&chr(39))  AS Field4
Resident TmpCombinations;
DROP TABLE TblCombinations;

Table with real field Names:

Field1

Field2

Field3

Field4

'<ANY>'

'<ANY>'

'<ANY>'

'<ANY>'

'<ANY>'

'<ANY>'

'<ANY>'

[SubProduct]

'<ANY>'

'<ANY>'

[SubRegion]

'<ANY>'

'<ANY>'

'<ANY>'

[SubRegion]

[SubProduct]

'<ANY>'

[Product]

'<ANY>'

'<ANY>'

'<ANY>'

[Product]

'<ANY>'

[SubProduct]

'<ANY>'

[Product]

[SubRegion]

'<ANY>'

'<ANY>'

[Product]

[SubRegion]

[SubProduct]

[Region]

'<ANY>'

'<ANY>'

'<ANY>'

[Region]

'<ANY>'

'<ANY>'

[SubProduct]

[Region]

'<ANY>'

[SubRegion]

'<ANY>'

[Region]

'<ANY>'

[SubRegion]

[SubProduct]

[Region]

[Product]

'<ANY>'

'<ANY>'

[Region]

[Product]

'<ANY>'

[SubProduct]

[Region]

[Product]

[SubRegion]

'<ANY>'

[Region]

[Product]

[SubRegion]

[SubProduct]

3. Calculate number of rows in our table (will use in cycle), after this we will be ready to load our data

Let vRowCount=NoOfRows('Combinations');


4. Load data in the cycle, for this we need create variable for every field

FOR i = 0 to vRowCount -1

vFieldName1 = peek('Field1',$(i),'Combinations');
vFieldName2 = peek('Field2',$(i),'Combinations');
vFieldName3 = peek('Field3',$(i),'Combinations');
vFieldName4 = peek('Field4',$(i),'Combinations');

AuthorizationBridge:

LOAD *

Where Exists (Auth, %AuthID);
LOAD Distinct

    AuthID,
    
$(vFieldName1) & '|' & $(vFieldName2) & '|' & $(vFieldName3) & '|' & $(vFieldName4) as %AuthID,
Resident Orders;

Next i;

DROP Tables Combinations, Orders;

We load all possible combinations and not all are valuable, so we can add condition

Where Exists (Auth, %AuthID);

In the preceding load – it will keep only combinations that we have in the Authorization table.

At the end we have Authorization Bridge table.

Result.png

Thanks,

Vitalii

 

Attachments
Comments
beck110979
Valued Contributor III

Thanks a lot for great example to issue: Access Section in QS

Version history
Revision #:
1 of 1
Last update:
3 weeks ago
Updated by: