topic Re: DO WHILE Loop thorugh Hierarchy in QlikView

DO WHILE Loop thorugh Hierarchy

Anonymous — Tue, 29 Aug 2017 09:39:13 GMT

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

Re: DO WHILE Loop thorugh Hierarchy

sasiparupudi1 — Tue, 29 Aug 2017 20:50:51 GMT

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

MarcoWedel — Wed, 30 Aug 2017 20:04:55 GMT

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

Re: DO WHILE Loop thorugh Hierarchy

Anonymous — Thu, 31 Aug 2017 13:28:23 GMT

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