How To Validate Section Access Table Content In QlikView

[ToniKautto]

For Qlik Sense see Troubleshooting Section Access with Groups in Qlik Sense

 

Environment:

QlikView  

QlikView Section Access feature can be used to limit user access to QlikView applications or limit access to the data inside the application.

If the access or data reduction does not work as expected, it is important to validate that the Section Access has been defined in a proper way. 

The Section Access table is stored a hidden system table in the QlikView application file. In order to analyse the section access setup, the table must be loaded as a normal table to enable evaluating the content.

Load Section Access (SA) as a normal table

1. Comment the Section Access keyword:
   

   //Section Access;​

2. Exit the scrip just before section application
   

   EXIT Script;
   Section Application;​

3. Disable SA Data Reduction under Document Properties > Opening > Initial Data Reduction Based on Section Access > Uncheck box
4. Reload the application.
5. The application should now only have one table loaded, which has the field that you defined in the section access script. Open Table Viewer (Ctrl + T) to confirm.
   
   

Visualize the Section Access table

1. Create a Straight Table object.
2. Add all fields from the section access table as dimensions, this includes all mandatory fields and the reduction fields
3. Add expressions to show that all fields are in upper case, for example as Upper(FIELDNAME)<>FIELDNAME for all fields that are defined in the section access table 
4. Add expressions to show that no field has null values, for example by checking that all entries have at least one character as Len(FIELDNAME)=0
5. Add an expression to show if there are duplicate records in the table, for example as Count(ACCESS)>1
6. Disable zero value suppression in Chart Properties > Presentation > Uncheck Suppress Zero-Values
   
   
   
   
7. All expressions have logical comparison, so that the result becomes 0 (FALSE) or -1 (TRUE). The chart will indicate inaccurate values with -1, and the table is accurate when there are only 0 values in the results. 
   
   
   
   
8. All -1 values in the image above indicate incorrect format or content in the section access table. The required measure is to make sure all data is valid and in proper format. 

IMPORTANT: All fields in the Section Access table must be validated, and not only the example fields above. 

Make all fields UPPER case

All data stored in the Section Access table must be in Upper case. This can be accomplished in two different ways.

1. Edit the data source so that all values are in upper case.
2. Use the Upper() function when loading the values to enforce upper case.

Avoid NULL values in reduction fields

The reduction field can not contain a undefined value, as then the reduction effect can not be predicted. Null values is a very undefined and abstract value which should never occur in a section access table.

If the reduction is expected to return all values, then the reduction value should be defined as wildcard (*) or a values that does not exist in the data tables reduction field. See Section Access documentation in QlikView Reference Manual for more details on how to include all values. 

Avoid NULL values in section access system fields

Null values or empty strings can not exist in the section access fields. In some setups the section access field can be populated with a wildcard (*), but otherwise the fields must always contain real values.

Eliminate duplicate rows

When all data formats are corrected, the last thing to do is eliminate duplicate rows in the section access table. There are two common ways to resolve this.

1. Remove duplicate entries in the data source
2. Make the section access table distinct, by using the DISTINCT predicate on the data load statement.