Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vchuprina
New Contributor II

Authorization bridge on multiple columns

  Good Morning,

 

I've read Henric blog about authorization bridge. It's very useful and interesting, but in example Henric used only two fields: REGION and PRODUCT. And when you reduce data based on two  fields it's not a problem to create authorization bridge table with all combinations, because you have only four combinations. How make data reduction if you should use 10 or more fields?

 

     Load Region &'|'& Product as AuthID, Region &'|'& Product as %AuthID  From OrderDetails ;

     Load Region &'|'& Product as AuthID, Region &'|'&'<ANY>' as %AuthID  From OrderDetails ;

     Load Region &'|'& Product as AuthID, '<ANY>'&'|'& Product as %AuthID  From OrderDetails ;

     Load Region &'|'& Product as AuthID, '<ANY>'&'|'&'<ANY>' as %AuthID  From OrderDetails

Basics for complex authorization

4 Replies

Re: Authorization bridge on multiple columns

Even, I don't like to create composite key using simple & (and operator). To get more buffer and excellent performance you could try this?

Load AutoNumber(Region &'|'& Product) as AuthID, AutoNumber(Region &'|'& Product) as %AuthID  From OrderDetails ;

Load AutoNumber(Region &'|'& Product) as AuthID, AutoNumber(Region &'|'&'<ANY>') as %AuthID  From OrderDetails ;

Load AutoNumber(Region &'|'& Product) as AuthID, AutoNumber('<ANY>'&'|'& Product) as %AuthID  From OrderDetails ;

Load AutoNumber(Region &'|'& Product) as AuthID, AutoNumber('<ANY>'&'|'&'<ANY>') as %AuthID  From OrderDetails

vchuprina
New Contributor II

Re: Authorization bridge on multiple columns

Anil,

You are right performance will be better.

Below you can see all combinations for 3 fields. Can you imagine how many combinations you we will have with 10 fields

 

Country &'|'& Region &'|'& Product 

'<ANY>' &'|'& Region &'|'& Product 

Country &'|'& '<ANY>'&'|'& Product

Country &'|'& Region &'|'& '<ANY>' 

'<ANY>' &'|'& '<ANY>'&'|'& Product

'<ANY>' &'|'& Region &'|'& '<ANY>' 

Country &'|'& '<ANY>'&'|'& '<ANY>'

'<ANY>' &'|'& '<ANY>'&'|'& '<ANY>'

 

Re: Authorization bridge on multiple columns

I'm not sure if it's really sensible to use 10 or more fields for the authorization. It's quite unusual to have so deep hierarchies - who is able to understand and handle them on the business side? If anyhow possible you should reduce the complexity maybe by using several applications for it.

Beside this if it's really too much work to write all load-statements manually you could create them on fly within some loops. I could imagine starting this by creating a cartesian product between all your fieldnames and ANY within a join and then looping through this table and picking each value with peek() into a variable(s) and combining/extending them to a complete load-statement and executing them.

Here a simple example for creating a fieldlist within a variable - you will need of course more efforts but it showed in which direction the task is going: Re: Interesting challenge: How to create a real table from a list of field names?

- Marcus

Re: Authorization bridge on multiple columns

I really don't know what you are going to have. As already Marcus states "I wonder how this can be sensible" to create with 10 columns. Instead, Why can't append using in external source rather Qlikview?

Community Browser