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
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
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:
Thanks again for the help
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
This works!
Thank you very much, you are really kind
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