topic Re: LOAD inside a WHILE / Section Access in QlikView

LOAD inside a WHILE / Section Access

Wed, 24 Jun 2015 15:10:47 GMT

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 !

Re: LOAD inside a WHILE / Section Access

Thu, 25 Jun 2015 06:50:19 GMT

Anyone to help me ?

Re: LOAD inside a WHILE / Section Access

jonathandienst — Thu, 25 Jun 2015 07:01:43 GMT

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';

Re: LOAD inside a WHILE / Section Access

Thu, 25 Jun 2015 07:05:47 GMT

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

Re: LOAD inside a WHILE / Section Access

jonathandienst — Thu, 25 Jun 2015 07:05:52 GMT

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

Unbalanced, n-level hierarchies

Re: LOAD inside a WHILE / Section Access

jonathandienst — Thu, 25 Jun 2015 07:09:38 GMT

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)...

Re: LOAD inside a WHILE / Section Access

Thu, 25 Jun 2015 07:18:07 GMT

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.

Re: LOAD inside a WHILE / Section Access

jonathandienst — Thu, 25 Jun 2015 07:22:46 GMT

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()

Re: LOAD inside a WHILE / Section Access

jonathandienst — Thu, 25 Jun 2015 07:38:59 GMT

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

Re: LOAD inside a WHILE / Section Access

Thu, 25 Jun 2015 08:51:08 GMT

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‌