No idea what these Levels represent or how they are used in your data model. Maybe you can attempt a solution like specified below. If no, please provide more details about your document or upload a representative sample qvw.
- Create a field containing a concatenation of Level1 Level2 Level3 Level4. If any of the later levels are NULL, then stop at the next lower level. Every fact record should have one field value.
- Add these field values to your SA table, or if the mix is too complex, create a translation table in Section Application that maps User IDs to all levels to which access should be granted.
As with everything in QlikView and due to the explicit-value-linking-associative logic of QlikView, you cannot select *nothing* (i.e. NULL) in the User Interface. Nor can you connect SA permissions to *nothing*. If you subsequently reduce your document, all those values will get thrown out anyway.
Replace them with some default value, like 'missing' or 'unknown'. If you don't like those values to appear in your listboxes and other objects, create Level X field copies in which you replace *nothing* with the selected default string value. Then create the concatenated Level X path from those values.
Am posting a sample data of my requirement.
there are two excel sheets.
one is Data - Data.xlsx - contains users data
other one is Section Access file - Section Access Data.xlsx - this tells which user should access what.
ACCESS NTName Level 1 (Country) Level 2 ( Region) Level 3 ( State) Level 4 (City) ADMIN A ADMIN B USER C Atlanta USER D Rajasthan USER E Patna USER F North USER G Japan USER H Kyushu
Consider the above table.
If ADMIN - Grant full access to all Country, Region, State, City
If USER check for below scenarios
- Check if he has an entry in City, if yes grant access to that City. say USER C must see only Atlanta data
- If city is null, then check if he has an entry in State, if yes grant access to that State. USER D must see only Rajasthan Data
- If both City and State is null, then Check for Region, if he has an entry in Region, then grant access to that Region. USER F must see only North Region Data
- If City, State, Region is null, then go for Country check and grant access to that Country data.
Still, an important distintion is left undefined.
If both City and State is null, then Check for Region, if he has an entry in Region, then grant access to that Region. USER F must see only North Region Data
Should USER F have access to all North reqions in all countires? If Yes, then there are 16 possible connections for every facts record. If No, then there are only 4.
So all lower levels are always specified (although they are not in your example). For example, to which country does Atlanta belong to according to your Section Access data? Also Japan? Because that's the only country available in your Excel data source...
If you expand your Section Access Data.xlsx source file to include values for all lower levels, then we create 5 mapping records for each row in Data.xlsx (Number of Level columns+1). For row 10 this set becomes (assuming imaginary wildcards for all missing level values):
Since USER F will have a corrected SA specification of
we are able to connect USER F to row 3 of your mapping data. USER A qnd USER B will be linked to mapping data row 5 and they will see this Data row as well. The others won't because there is no match.
NOTE: as I already stated before, you will have to merge all Level values into a single consistent Level Patch field for this mechanism to work. Of course, you are free to leave the original Level X fields as they are.