Linking Section Access to multiple dimensions

3 min read

(For more resources related to this topic, see here.)

Getting ready

Load the following script:

Product:
LOAD * INLINE [
ProductID, ProductGroup, ProductName
1, GroupA, Great As
2, GroupC, Super Cs
3, GroupC, Mega Cs
4, GroupB, Good Bs
5, GroupB, Busy Bs
];

Customer:
LOAD * INLINE [
CustomerID, CustomerName, Country
1, Gatsby Gang, USA
2, Charly Choc, USA
3, Donnie Drake, USA
4, London Lamps, UK
5, Shylock Homes, UK
];

Sales:
LOAD * INLINE [
CustomerID, ProductID, Sales
1, 2, 3536
1, 3, 4333
1, 5, 2123
2, 2, 4556
2, 4, 1223
2, 5, 6789
3, 2, 1323
3, 3, 3245
3, 4, 6789
4, 2, 2311
4, 3, 1333
5, 1, 7654
5, 2, 3455
5, 3, 6547
5, 4, 2854
5, 5, 9877
];

CountryLink:
Load Distinct
Country,
Upper(Country) As COUNTRY_LINK
Resident
Customer;

Load Distinct
Country,
'ALL' As COUNTRY_LINK
Resident
Customer;

ProductLink:
Load Distinct
ProductGroup,
Upper(ProductGroup) As PRODUCT_LINK
Resident
Product;

Load Distinct
ProductGroup,
'ALL' As PRODUCT_LINK
Resident
Product;

//Section Access;

Access:
LOAD * INLINE [
ACCESS, USERID, PRODUCT_LINK, COUNTRY_LINK
ADMIN, ADMIN, *, *
USER, GM, ALL, ALL
USER, CM1, ALL, USA
USER, CM2, ALL, UK
USER, PM1, GROUPA, ALL
USER, PM2, GROUPB, ALL
USER, PM3, GROUPC, ALL
USER, SM1, GROUPB, UK
USER, SM2, GROUPA, USA
];

Section Application;

Note that there is a loop error generated on reload because there is a loop in the data structure.

How to do it…

Follow these steps to link Section Access to multiple dimensions:

Add list boxes to the layout for ProductGroup and Country. Add a statistics box for Sales.
Remove // to uncomment the Section Access statement.
From the Settings menu, open Document Properties and select the Opening tab. Turn on the Initial Data Reduction Based on Section Access option.
Reload and save the document. Close QlikView.
Re-open QlikView and open the document. Log in as the Country Manager, CM1, user. Note that USA is the only country. Also, the product group, GroupA, is missing—there are no sales of this product group in USA.
Close QlikView and then re-open again. This time, log in as the Sales Manager, SM2. You will not be allowed access to the document.
Log into the document as the ADMIN user. Edit the script. Add a second entry for the SM2 user in the Access table as follows:

USER, SM2, GROUPA, USA

USER, SM2, GROUPB, UK
Reload, save, and close the document and QlikView. Re-open and log in as SM2. Note the selections.

How it works…

Section Access is really quite simple. The user is connected to the data and the data is reduced accordingly. QlikView allows Section Access tables to be connected to multiple dimensions in the main data structure without causing issues with loops.

Each associated field acts in the same way as a selection in the layout. The initial setting for the SM2 user contained values that were mutually exclusive. Because of the default Strict Exclusion setting, the SM2 user cannot log in.

We changed the script and included multiple rows for the SM2 user. Intuitively, we might expect that, as the first row did not connect to the data, only the second row would connect to the data. However, each field value is treated as an individual selection and all of the values are included.

There’s more…

If we wanted to include solely the composite association of Country and ProductGroup, we would need to derive a composite key in the data set and connect the user to that.

In this example, we used the USERID field to test using QlikView logins. However, we would normally use NTNAME to link the user to either a Windows login or a custom login.