3 Replies Latest reply: Aug 31, 2017 9:28 AM by Stefan Habermacher RSS

    DO WHILE Loop thorugh Hierarchy

    Stefan Habermacher

      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
          Sasidhar Parupudi

          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
            Marco Wedel

            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
              Stefan Habermacher

              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