Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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;

Community Browser