DO WHILE Loop thorugh Hierarchy

[Anonymous]

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

[sasiparupudi1 · Accepted solution]

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]

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