Hierarchy

Thu, 13 Dec 2012 17:42:19 GMT

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, 😎 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;

topic Hierarchy in QlikView

Hierarchy