can i get this out of my hierarchy?

[amien]

i have created a hierarchy using qlikview's HIERARCHY functions. All works fine.

but now i want to create a pivot/table with two columns:

column 1: all first level material

column 2: all the materials, on the master material from column1, without any level lower. So this can be a level 3 material, but also a level 5 material.

example:

mat1 - mat2

mat1 - mat3 - mat4

mat1 - mat5

mat1 - mat6 - mat7

mat1 - mat8 - mat9 - mat10

my new list will be

mat1 - mat2

mat1 - mat4

mat1 - mat5

mat1 - mat7

mat1 - mat10

i tried it with subfield on the pathname .. but will all the material that are under mat1. including mat3 for example, which is nice, but not what i need

[#2]

Does this hep?

Regards,

Kiran Rokkam.

[amien]

almost .. the problem is that i also have these lines in my pathname column:

mat1-mat3

mat1-mat8

[#4]

What lines are there? You  can use the appropriate delimiter in the expression. Can you share sample of your column?

Regards,

Kiran Rokkam.

[amien]

Thanks for your reply

OR ROUGE [7.0 UN]

OR ROUGE [7.0 UN]|ROUGE CONC BULK

OR ROUGE [7.0 UN]|ROUGE CONC BULK|ROUGE CONC

OR ROUGE [7.0 UN]|ROUGE CONC BULK|ROUGE CONC|ROUGE ORANGE

This one for example. this is an example from pathname. I need all these lines to create the listbox and make it expandable.

with your solution i will get 3 rows back. i only need 1 : OR ROUGE [7.0 UN] as head material and ROUGE ORANGE as lowest node which is not expandable.

your solution gives me a list of all unique materials that are under OR ROUGE [7.0 UN].

It's possible that on level 2 there is another item .. then i also need the lowest node which is not expandable.

[#6]

If there are only four levels possible, in your script filter the rest. This can be done by adding the condition in the where  clause as (assuming the field name is pathname):

Where SubStringCount(pathname,'|') = 3.

Regards,

Kiran Rokkam.

[amien]

Well that is just about the issue .. it can be on only level

[#8]

The max number of levels also for each level 1 item can be calculated in the script. I didnt get your requirements clearly. If you can have the senario better explained liked: source data (excel) and expected output we can help you.

Regards,

Kiran Rokkam.

[swuehl]

Maybe like this?

HierarchySplit:

LOAD Materials, subfield(Materials,'-',1) as Level1Mat, subfield(Materials,'-',2) as Level2Mat, subfield(Materials,'-', iterno()) as PartMat, IterNo() as Level while iterno() <= SubStringCount(Materials,'-')+1;

LOAD * Inline [

Materials

mat1-mat2

mat1-mat2-mat4

mat1-mat2

mat1-mat2-mat7

mat1-mat2-mat9-mat10

mat1-mat3-mat4

mat1-mat3

mat2-mat3-mat4

mat1-mat3-mat5

mat3

mat4-mat1

];

LOAD Level1Mat, Level2Mat, FirstSortedValue(distinct PartMat, -Level) as LowestItem resident HierarchySplit group by Level1Mat, Level2Mat;

Regards,

Stefan

[amien]

appriciate your reply swuehl,

but i'm missing mat5 and 7. both nodes are the lowest and can't be expended.

i created a qvw, base on the above example, with a real example. in the textbox you will find the results that i need