0 Replies Latest reply: Dec 13, 2012 12:42 PM by Sean Smith RSS

    Hierarchy

      I am having an issue with Hierarchy.  My script will take our product Hierarchy and break it out to 5 levels, Level 1 through Level 5.  So lets say my product hierarchy field is up to 10 char and each individual level is 2 char.  So, if the field value has 10 char in it, each Level will have 2 char in it and if the product hierarchy field only has 2 char in it, then only Level 1 will have 2 char in it and there will not be a Level 2-5.  The issue that I have is in making selections with a button.  I set a button to select values from Level 1 and to exclude certian values from level 2.  Trouble is, if one of the values in Level 1 does not have any values in Level 2 it is dropped from the selection since there is no coresponding Level2 value.  Since many of my prod hierarchy's do not go out to Level 5, how can I account for this in my script so my selections are not dropped?

       

      Below is the script in the data model.  I did not write this and I have never worked with Hierarchy in QlikView so any help on this topic would be appreciated. 

       

      Thanks,

       

      PH1:

      LOAD DISTINCT [Product hierarchy_PRDHA] as tPRODH RESIDENT Material;

       

       

      CONCATENATE (PH1)

      LOAD DISTINCT left(tPRODH, 12) as tPRODH RESIDENT PH1 where len (tPRODH) = 15;

       

       

      CONCATENATE (PH1)

      LOAD DISTINCT left(tPRODH, 8) as tPRODH RESIDENT PH1 where len (tPRODH) = 12;

       

       

      CONCATENATE (PH1)

      LOAD DISTINCT left(tPRODH, 4) as tPRODH RESIDENT PH1 where len (tPRODH) = 8;

       

       

      CONCATENATE (PH1)

      LOAD DISTINCT left(tPRODH, 2) as tPRODH RESIDENT PH1 where len (tPRODH) = 4;

       

       

      P1:

      LEFT Keep (PH1)

      LOAD

           [Product hierarchy_PRODH] as tPRODH,

           [Level number_STUFE] as STUFE

      FROM \\ghos2280\repository$\02.CONFIG\PROD_HIERARCHY\DATA\T179.qvd (qvd);

       

       

       

       

       

       

       

       

      Left join (P1)

      LOAD

           [Product hierarchy_PRODH] as tPRODH,

           Description_VTEXT as VTEXT

      FROM \\ghos2280\repository$\02.CONFIG\PROD_HIERARCHY\DATA\T179T.qvd (qvd)

      WHERE [Language Key_SPRAS] = 'E';

       

       

       

       

      ProdHie:

      HIERARCHY ([Product hierarchy_PRDHA], PARENT_PRODH, LEVEL, ParentName, 'ProdHie_Desc', ProdHiePath, '~')

      LOAD tPRODH as [Product hierarchy_PRDHA],

           if(STUFE=2, left(tPRODH,2),

              if(STUFE=3, left(tPRODH,4),

                                    if(STUFE=4, left(tPRODH,8),

                                              if(STUFE=5, left(tPRODH,12)

              )))) as PARENT_PRODH,

           STUFE as LEVEL,

           VTEXT as ProdHie_Desc

      RESIDENT P1;

       

       

      DROP Tables PH1, P1;

      DROP Fields PARENT_PRODH, LEVEL, ParentName;

       

       

      LEFT JOIN (ProdHie)

      LOAD [Product hierarchy_PRDHA],

                 subfield(ProdHiePath, '~', 1) as Ph1Text,

                 subfield(ProdHiePath, '~', 2) as Ph2Text,          

                 subfield(ProdHiePath, '~', 3) as Ph3Text,

                 subfield(ProdHiePath, '~', 4) as Ph4Text,

                 subfield(ProdHiePath, '~', 5) as Ph5Text

      RESIDENT ProdHie;