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.
I examine this in more depth on my blog https://adventuresinqlik.com/2018/03/11/the-problem-with-section-access-in-sense-with-workarounds/ but the basics of my suggested solutions are below...
In QlikView if you introduced section access 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. 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.
Some possible workarounds: !
- 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.
- 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.
- Split into 2 apps – I’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.
- 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).
- 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.
- 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.
