Revisiting the QlikView Cookbook - Containers as Multiboxes

It has been 7 years since the QlikView for Developer's Cookbook was published! 

The book, which was very much targeted at QlikView developers, is still available. Just for fun, I thought that it might be interesting to write a series of posts looking back at some of those recipes and seeing how they could be recreated in Qlik Sense Business.

In this post, I'll look at the recipe on Using containers as an alternative to multiboxes.

For those who are unfamiliar with the QlikView Multibox, it was a way for us to add multiple filters into a relatively small area:

When a user clicked on the Dimension name, the relevant List box would pop out. This is not dissimilar to how the Filter pane works in Qlik Sense when there are several Dimensions added and not enough room to display values:

The Cookbook presented an alternate option whereby a Container is used to contain multiple List Boxes:

It worked quite well (in my humble opinion!)

Now, there is also a Container object in Qlik Sense, so we can achieve something similar. This is the load script from the Cookbook:

Data:
LOAD * INLINE [
  Country, Product, SalesPerson, Category, Sales
  USA, Widget, Joe, Widgets, 1234
  USA, Woggle, Joe, Widgets, 1983
  USA, Brogue, Jane, Footwear, 1175
  USA, Clog, Jane, Footwear, 1376
  UK, Widget, Tom, Widgets, 954
  UK, Woggle, Tom, Widgets, 953
  UK, Brogue, Tom, Footwear, 989
  UK, Clog, Tom, Footwear, 875
  Japan, Widget, Mike, Widgets, 1265
  Japan, Woggle, Mike, Widgets, 1345
  Japan, Brogue, Jane, Footwear, 1425
  Japan, Clog, Jane, Footwear, 1324
];

Loading this in Qlik Sense Business, we can then add a Container and 4 separate Filter Pane objects for the 4 Dimensions:

If you don't have too much real estate to devote to it, then it will automatically collapse to a version with a menu to select the Dimension:

I prefer this to the default Filter Pane method.

Worth noting that the default would be to have "Sales Person" twice - as a Title for the Container and for the Filter Pane - you can blank out the Filter Pane's title to make it go away.

---

As well as holding a Master's Degree in Data Analytics, Stephen Redmond is a practicing Data Professional of over 20 years experience. He is author of Mastering QlikView, QlikView for Developer's Cookbook and QlikView Server and Publisher.  
Follow @stephencredmond  

Revisiting the QlikView Cookbook - Section Access Gotchas

It has been 7 years since the QlikView for Developer's Cookbook was published! 

The book, which was very much targeted at QlikView developers, is still available. Just for fun, I thought that it might be interesting to write a series of posts looking back at some of those recipes and seeing how they could be recreated in Qlik Sense Business.

In this post, I'll look at the recipe on Section Access Gotchas. In Qlik, Section Access is the area of script where we connect users to the data to allow row-level security. It is very powerful and relatively easy to implement for many typical use cases.

Before diving in, it is worth pointing out that Qlik Sense Business has a great feature, missing from QlikView when I was writing the Cookbook, that means if a Section Access script would result in the locking out of the developer, then tags the reload as unsuccessful and won't save the faulty setup. This will help with a lot of Section Access issues!

In the recipe I showed a number of different issues. Starting off with this load script:

Section Access;
Access:
LOAD * INLINE [
  ACCESS, USER.EMAIL, Link
  ADMIN, stephen.redmond@notmymail.fake, *
  USER, user1@notmymail.fake, US
  USER, user2@notmymail.fake, UK
  USER, user3@notmymail.fake, Fr
];
Section Application;
Sales:
Load * Inline [
  Link, Country, Sales
  US, USA, 1000
  UK, United Kingdom, 800
  Fr, France, 750
  De, Germany, 940
];

Now, when I login as the Admin, I get to see all the data as expected. However, when I login as user1, I still see all the data when I was expecting to only see US data! What is going on?

Well, a simple rule on the Section Access tables is that all the fields must be in capital letters. Simple fix so:

Section Access;
Access:
LOAD * INLINE [
  ACCESS, USER.EMAIL, LINK
  ADMIN, stephen.redmond@notmymail.fake, *
  USER, user1@notmymail.fake, US
  USER, user2@notmymail.fake, UK
  USER, user3@notmymail.fake, Fr
];
Section Application;
Sales:
Load * Inline [
  LINK, Country, Sales
  US, USA, 1000
  UK, United Kingdom, 800
  Fr, France, 750
  De, Germany, 940
];

Updating the Link to LINK in both tables and now User1 only sees US data. What of user3? If I log in as User3 I get Access Denied! This is because of a second Gotcha - any data field that links users to data and that contains letters, must only contain capital letters! So, "Fr" doesn't work. Simple fix:

Section Access;
Access:
LOAD * INLINE [
  ACCESS, USER.EMAIL, LINK
  ADMIN, stephen.redmond@notmymail.fake, *
  USER, user1@notmymail.fake, US
  USER, user2@notmymail.fake, UK
  USER, user3@notmymail.fake, FR
];
Section Application;
Sales:
Load * Inline [
  LINK, Country, Sales
  US, USA, 1000
  UK, United Kingdom, 800
  FR, France, 750
  DE, Germany, 940
];

Now, User3 can login and see the France data. All is great. Or is it?

But let us just quickly log back in as the Admin - we can only see US, UK and France! Where is Germany? This is a Gotcha whereby all of the users must be linked to all of the data or the security will remove access to the unlinked data. Interestingly, also having an admin user who is linked to none of the data will also work. It does look like the Admin user is linked to everything via the "*", but that isn't actually everything - it is only everything that is linked! Confused yet?

We can fix it like this:

Section Access;
Access:
LOAD * INLINE [
  ACCESS, USER.EMAIL, LINK
  ADMIN, stephen.redmond@notmymail.fake, NONE
  USER, user1@notmymail.fake, US
  USER, user2@notmymail.fake, UK
  USER, user3@notmymail.fake, FR
];
Section Application;
Sales:
Load * Inline [
  LINK, Country, Sales
  US, USA, 1000
  UK, United Kingdom, 800
  FR, France, 750
  DE, Germany, 940
];

Now, the Admin user can see everything. The "NONE" is arbitrary and can be any value as long as it doesn't exist in the data. Actually, I prefer to script things so that at least one user is linked to all of the data values. That way I don't have to rely on the "*".

---

As well as holding a Master's Degree in Data Analytics, Stephen Redmond is a practicing Data Professional of over 20 years experience. He is author of Mastering QlikView, QlikView for Developer's Cookbook and QlikView Server and Publisher.  
Follow @stephencredmond