The Problem With Section Access in Sense (With Workarounds)

Section Access Header Image  We’ve all experienced pain when using section access.  Qlik Sense addressed one of the headaches by allowing you to right-click on an app and “open without data” in order to access your script if you lock yourself out.  However, Sense has introduced a problem that never existed in QlikView; that omitted fields cause visualisations to fail.  

In QlikView if you introduced section access (the means by which you can create rules to limit the data in your app for certain users for security purposes) and invoked OMIT fields (a list of fields which the user should not have visability of) any charts which contained omitted fields would still show up, just without the fields in question.  This was perfect for tables containing sensitive data where you needed to hide certain fields from sight.  In Qlik Sense the chart will fail and display an ‘incomplete’ visualisation error.  Essentially, Sense has looked for the field and hasn’t found it because it has been omitted so it cannot render the chart.  I suspect that the underlying reason for this is that QlikView supports show/hide columns whereas Qlik Sense does not.  In Sense it’s show all or show nothing!

This creates a problem for anyone developing an app with sensitive data, especially if you’re converting a QlikView app to Qlik Sense.  Qlik’s help page has this to say on the matter:


So what can we do to get around this issue?  I’ll list the options that have come to my mind and if you can think of any others please share them in the comments below.

  1. Wait for Qlik to resolve the issue – show/hide columns has been mentioned as a roadmap item before but there can be no guarantees when/if this will find its way into the product.
  2. Use IF or Pick functions to create dynamic columns – after loading your section access tables load a new table for front end/UI consumption containing UserName and a flag to indicate if they should have fields hidden.  In the following example I arranged groups of users who had the same list of fields to be omitted into groups called OMITGROUP.  Create a variable in the UI to return the username=textbetween(OSUser(),’=’,’;’) & ‘\’ & upper(SubField(OSUser(),’=’,3)) and call it vUser.  Then create another variable to work out which ommission group they belong to  =only({<SA_USERID={‘$(vUser)’}>}SA_OMITGROUP) and call it vUserOmit.  You can then reference this variable in your chart or table =if(isnull(vUserOMIT),FieldToShow/Hide,Null()).  In this example the formula will hide the data in this field if the user belongs to any omission group but show it if they don’t belong to any omission group.  The drawback with this solution is the addition calculation time the above calculations add – best to test any impact on CPU.
  3. Split into 2 appsI’m not a fan of this approach but you could decide to create a non-sensitve data app and a sensitive data app and control who can access them via the QMC.  The reason I’m not a fan is the duplication of effort it takes to implement any changes to the apps.
  4. Put the sensitive fields into a separate table and show it alongside the original this could work if you ensure you have the same key field in both tables (e.g. customer ID), they are both sorted the same and there are no fields that cause duplication in one table but not the other.  That way your total number of rows will match up.  This wil work will when filtering for a particular result but will be a nightmare when scrolling through one table and not seeing the other move.  It would also prove problematic if you needed to export to excel (you’d need to run a vlookup to join the data back together).
  5. Use/build an extension with show/hide columns as a feature – if you or your client are happy to use extensions there are some out there with show/hide columns but you might still need to write in the formulas from solution #2 as your show/hide conditions.  Qlik Branch is the best port of call for looking at existing extensions.
  6. Use show/hide containers to house a version containing the sensitive fields and version without them – This is one example.  You could use a similar forula to that in solution #2 to determine which table they should see.

So as you can see there are few different way to approach this issue.  Each have their own advantages and disadvantages but I hope this gives you food for thought if you ever need to use OMIT in section access.

One thought on “The Problem With Section Access in Sense (With Workarounds)

  1. Thanks for the post. Here is another alternative along the lines of option 2. Assuming that your OMIT field name is called Sensitive you can use this in the Dimension expression of a table.

    =if(not isnull(fieldvalue(‘Sensitive’, 1)), Sensitive)


    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s