Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
habermst
New Contributor II

DO WHILE Loop thorugh Hierarchy

Hello Qlik Community!

The following situation:

We get a csv file each month with the current organizational structur. I load all those files into qlikview and generate a hierarchy. So far so good. Now i'd like to generate one table for each depth / level. Currently there are 4 ( 1 - 4) levels. I could just say WHERE LEVEL =1 and so on. But the problem is, that the depth can change (levels can be added or removed).

Something like this:

EDIT:


I got the Loop part:

QUALIFY * ;

LET vDepth = 4;

Do while vDepth > 0

LET vTableName = 'Level' & $(vDepth);

$(vTableName):

NoConcatenate

LOAD

*

Resident

OrgHierarchyTmp

WHERE Depth = $(vDepth);

LET vDepth = vDepth - 1;

Loop;

This is working.

The only problem left: If I try to assign the vDepth dynamically like this:

LET vDepth = 'max(Depth)';

I get an error.

SET vDepth = 'max(Depth)';

is not working neither.


Any ideas?

Thanks & best Regards

1 Solution

Accepted Solutions
sasiparupudi1
Honored Contributor III

Re: DO WHILE Loop thorugh Hierarchy

You have to use Peek function to get the max(depth)

maxTable:

LOAD

max(depth) as maxDepth

Resident

OrgHierarchyTmp;

let vMaxDepth=Peek('maxDepth',0,('maxTable');

hth

Sasi

3 Replies
sasiparupudi1
Honored Contributor III

Re: DO WHILE Loop thorugh Hierarchy

You have to use Peek function to get the max(depth)

maxTable:

LOAD

max(depth) as maxDepth

Resident

OrgHierarchyTmp;

let vMaxDepth=Peek('maxDepth',0,('maxTable');

hth

Sasi

Re: DO WHILE Loop thorugh Hierarchy

Hi,

instead of

Do while vDepth > 0

  ...

  LET vDepth = vDepth - 1;

Loop;

with initial Depth max value you might as well use a For ... Next loop with FieldValueList

FOR Each vDepth in FieldValueList('Depth')

  ...

NEXT vDepth

hope this helps

regards

Marco

habermst
New Contributor II

Re: DO WHILE Loop thorugh Hierarchy

Hello Sasi & Marco

Thanks a lot for your inputs. It Works now as it should!

maxTable:

LOAD

max(Depth) as MaxDepth

Resident

OrgHierarchyTmp;

Let vDepth = Peek('MaxDepth',0,('maxTable'));

QUALIFY * ;

UNQUALIFY [%*];

For each vMaxDepth in  FieldValueList('Depth') 

LET vTableName = 'Level' & $(vDepth);

LET vKeyName = '%ID_Datum_Level3' & $(vDepth);

IF vDepth >1 THEN

LET vKeyUp   =  vDepth - 1;

LET vKeyDown =  vDepth + 1;

LET vKeyNameUp   = '%Parten_ID_Datum_Level' & $(vDepth);

LET vKeyNameDown = '%Parten_ID_Datum_Level' & $(vKeyDown);

ELSE

LET vKeyNameUp   = '%ID_Datum_Level_UP_MAX';

LET vKeyNameDown = '%Parten_ID_Datum_Level2';

ENDIF

$(vTableName):

NoConcatenate

LOAD

Parent_ID & '_' & Datum as $(vKeyNameUp),

ID & '_' & Datum as $(vKeyNameDown),

*

Resident

OrgHierarchyTmp

WHERE Depth = $(vDepth);

LET vDepth = vDepth - 1;

Next vMaxDepth;

Thanks again & Best Regards

Community Browser