LOAD inside a WHILE / Section Access

[#1]

Hi All,

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 !

[#2]

Anyone to help me ?

[jonathandienst]

The WHILE in the LOAD does not have an IterNo() associated with it, and nothing is driving the other two, so they are always zero. Can you not achieve that with a simple resident load:

AUTH: 

LOAD 

  'USER' as ACCESS, 

  '00211' as PASSWORD, 

  REFERENCE as RESTRICTION 

RESIDENT AUTHRESIDENT 

WHERE LEVEL <> '0'; 

[#4]

But I have to store the REFERENCE of the user with LEVEL='0' before that, so a WHILE is necessary no ?

[jonathandienst · Accepted solution]

I would load the ParentID and use the Qlikview hierarchy functions. See here for more:

Unbalanced, n-level hierarchies

[jonathandienst]

I don't really understand your question.

"While" is used to insert additional records in the LOAD statement, such as filling between dates, usually using IterNo() as a counter in a field expression and possibly in the while condition.

"Where" is a filter to load records that match the filter(s)...

[#7]

Yes, I understand, I was speaking about the DO WHILE which "surround" the LOAD.

With this WHERE condition, it will only LOAD users with LEVEL='0' but I want to LOAD all users with the REFERENCE of the previous user with LEVEL='0'.

Thank you, I will see for hierarchies within Section Access.

[jonathandienst]

In the article line I posted above, Henric Cronstrom explains the best way to do hierarchy analysis in QV.

Unbalanced, n-level hierarchies

If you follow his advice, what you need to do will be easy.

Oh, and DO WHILE does not use or update IterNo()

[jonathandienst]

Create a Nodes table as in the article, then link the section access on the correct level node field.

[#10]

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.

Thank you for your help jontydkpi‌