Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Master III
Master III

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

View solution in original post

3 Replies
sasiparupudi1
Master III
Master III

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

MarcoWedel

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

Anonymous
Not applicable
Author

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