topic Analyze a simple bill of materials. Peek, Previous or what? in App Development

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

cr7dav — Fri, 17 Jun 2022 15:58:14 GMT

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

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

MarcoWedel — Fri, 17 Jun 2022 21:22:33 GMT

maybe helpful:

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

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

cr7dav — Fri, 17 Jun 2022 23:43:59 GMT

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

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

MarcoWedel — Sat, 18 Jun 2022 09:55:02 GMT

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

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

cr7dav — Sat, 18 Jun 2022 13:44:43 GMT

This works!

Thank you very much, you are really kind

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

cr7dav — Tue, 28 Jun 2022 12:36:46 GMT

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