While inside LOAD / Section Access in Qlikview

Question

I have an hierarchy Table which I try to add in QlikView in order to use RESTRICTION (Section Access) on users to show/hide data.

On the Table, I have the user REFERENCE, with an ID which is linked to an IDPARENT in order to have an hierarchy.

I want that all the user "on top" of the leaf user (the last one in the hierarchy) could have access on his data.

In order to do this, I use this SQL query :

Select
REFERENCE,
LEVEL-1 "LEVEL",
from HIERARCHYTABLE
start with TYPE='VD'     //Start hierarchy with all the users with type='VD'
connect by ID =  prior IDPARENT;

It return :

VD254       0
 IG203      1
   GR203    2
VD255       0
 IG232      1
   GR258    2
 IG235      1
   GR259    2

-> So IG203 and GR203 can access to the VD254 data

And IG232, GR258, IG235 and GR259 can access to the VD255

With that, I have all my REFERENCE associated to the LEVEL (Here the user on the bottom is the level "0").

I want my AUTHENTIFICATE table to be like :

 ACCESS, REFERENCE, PASSWORD, RESTRICTION
 ADMIN, ADMIN, ADMIN, *
 USER, VD254, VD254, VD254
 USER, VD254, VD254, VD255 // Here the user VD254 can access to his data and the VD255 user data

So I was thinking of that when I have a 0 LEVEL, all the next users would have the REFERENCE of the 0 level in RESTRICTION until the loop see another 0 LEVEL :

But I can't make it works :

. The IterNo() is alway at '0' so the IF condition is alway true with the wrong value !

. It add an infinite number of rows to AUTH, so LEVEL <> '0' is never true...

AUTHRESIDENT:
LOAD
REFERENCE,
LEVEL,
Select
REFERENCE,
LEVEL-1 "LEVEL",
from HIERARCHYTABLE
start with TYPE='VD'     //Start hierarchy with all the users with type='VD'
connect by ID =  prior IDPARENT;

Let vRowCount = NoOfRows('AUTHRESIDENT');

DO WHILE (IterNo() <> vRowCount)
IF peek('LEVEL', IterNo(), 'AUTHRESIDENT')='0' THEN
    Let vNumVd = peek('REFERENCE', IterNo(), 'AUTHRESIDENT');
    AUTH:
    LOAD
        'USER' as ACCESS,
        '00211' as REFERENCE, // Only to test
        '00211' as PASSWORD,
        $(vNumVd) as RESTRICTION
    RESIDENT AUTHRESIDENT
    WHILE LEVEL <> '0';

ENDIF;
LOOP;

Thank you a lot for your help !

Answer 1

I finally found the solution with the unbalanced hierarchy, here is my code for people in the same case :

HIERARCHY:
HierarchyBelongsTo(IDNOEUD, IDNOEUDPARENT, REFERENCE, TreeID, TreeName)
LOAD
REFERENCE,
IDNOEUD,
IDNOEUDPARENT,
Select
REFERENCE,
IDNOEUD,
IDNOEUDPARENT,
from HIERARCHYTABLE;
Trees:
LOAD
*,
Upper(TreeName) as PERMISSION,
REFERENCE as MYPERMISSIONFIELD // Field which is the filter
Resident HIERARCHY;
Drop Table HIERARCHY;

Section Access;
AUTH:
LOAD * INLINE [
ACCESS, USERID, PASSWORD, PERMISSION
ADMIN, ADMIN, ADMIN, * // To add the ADMIN !
];

AUTH:
LOAD
'USER' as ACCESS,
REFERENCE as USERID,
REFERENCE as PASSWORD,
UPPER(REFERENCE) as PERMISSION;
SELECT
REFERENCE
FROM HIERARCHYTABLE;

Section Application;

Then the PERMISSION is linked to all the MYPERMISSIONFIELD.