Analyze a simple bill of materials. Peek, Previous or what?

[cr7dav]

Hi all,

I have this simple bill of materials:

Level	Code
1	code1
2	code2
2	code3
2	code4
3	code5
3	code6
3	code7
4	code8
2	code9

I'd like to make a script in Qlik Sense that add a column with the the father product of the code, this is what I'd like to obtain:

Level	Code	product
1	code1
2	code2	code1
2	code3	code1
2	code4	code1
3	code5	code4
3	code6	code4
3	code7	code4
4	code8	code7
2	code9	code1

I can use

if(Level>peek(Level), peek(MainProduct)) as MainProduct

but this doesn't work in the last row... How can I solve this?

Thanks in advance.

Davide

[MarcoWedel]

maybe helpful:

https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-split-parent-child-column/m-p/1933881

[cr7dav]

Hi,

thanks for your reply!

No, I really can't understand how can I use the hierarchy function in this case...

EDIT: maybe I solve the problem thanks to your help! Some questions:

• If I have a table like the one in the example in the first post, I can generate the ID with RowNo()? Is that correct?
• to obtain the name of the parent, instead of the parentid, I can use the join with the original table? 
• why If I make a left join on the first table, before the hierarchy function, in order to add a description column,  the function doesn't work anymore? If I move the join after the hierarchy all works well, but why? The join function is with a table with distinct values so doesn't add rows, only a column...

Thanks again for the help

[MarcoWedel · Accepted solution]

Hi,

it might be easier than you describe. Here's an adaptation of my previous example to your input data:

table1:
Hierarchy (Code, Product, Level_)
LOAD *,
Code as Level_,
Text(Left(Peek(Path)&'/',Index(Peek(Path)&'/','/',Level-1))&Code) as Path,
SubField(Peek(Path),'/',Level-1) as Product
Inline [
Level, Code
1, code1
2, code2
2, code3
2, code4
3, code5
3, code6
3, code7
4, code8
2, code9
];

including also the path and different level fields

hope this helps

Marco

[cr7dav]

This works!

Thank you very much, you are really kind

[cr7dav]

Hi Marco,

If in original table I have another column with the quantity of the single code of that row, how can I add the total quantity in the output table, where the value is multiplicated for the quantity of the parents?

Thanks