Data Reduction Using Multiple Fields - Qlik Community

hic · ‎2014-06-03

Last week I wrote about authorization using Section Access and data reduction. In the example, a user was associated with a country and this entry point in the data model determined whether a record was visible or not: Only records associated with the country were visible. COUNTRY was the reducing field.

But if you want several reducing fields? You may have a user that should see one product group in one country, and another product group in another country. Then both COUNTRY and PRODUCTGROUP should be reducing fields. Is that possible?

Yes and No.

Yes, it is possible. But No, you cannot just add a second reducing field. In simple cases, just adding a second reducing field will work fine, but as soon as the logic is slightly more complex, it will not work. Below I’ll explain why.

Let’s use the following example: A user MARKUS should see the product group Machines in Germany and Food in France – but not the other way around. Then the obvious approach would be to create an authorization table like the following:

I.e. two reducing fields – COUNTRY and PRODUCTGROUP – defining the following logic:

(‘GERMANY‘ AND ‘MACHINES’) OR (‘FRANCE’ AND ‘FOOD’)

However, this will not work in QlikView.

If you do the above, you will get a data model like the following (where the red table is the Section Access table).

This means that the selections in the real data model will be made in COUNTRY and in PRODUCTGROUP. But remember that QlikView always uses OR-logic between values of the same field and AND-logic between fields. This means that QlikView will interpret the selection in these two fields as

(‘GERMANY‘ OR ‘FRANCE’) AND (‘MACHINES’ OR ‘FOOD’)

which is not the same as the initial requirement. The selection made will include Machines in France and Food in Germany, which is against the initial requirement. In fact, it is impossible to make a selection that corresponds to the initial requirement using only these two fields.

So what should you do?

The solution is to create a new, single reducing field based on COUNTRY and PRODUCTGROUP, e.g. through

COUNTRY & ‘|’ & PRODUCTGROUP as AUTHORIZATIONKEY

The first challenge is to find the table where this key should be created. It must be in a table with a grain fine enough that both country and product group are uniquely defined on each record. In the above data model this is the Order Details table: Each order line has only one product and one customer – thus one product group and one country – associated with it. So, the authorization key should be created here.

There are other challenges also, e.g. how to get the country and product group information into the load of the Order Details table (Solution: ApplyMap) and how to handle the concept of Any Value (Solution: Generic Keys), but these can all be solved. For details, see the blog post about Complex Authorization.

Bottom line: You can use multiple reducing fields in Section Access, but only if each user has only one record in the authorization table. If a user has several records, you need to create one single authorization key.

Also, the above example clearly shows that authorization is part of the data modelling and should be thought of at an early stage in the development process.

HIC