Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
cr7dav
Contributor II
Contributor II

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

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

Labels (1)
1 Solution

Accepted Solutions
MarcoWedel

Hi,

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

MarcoWedel_1-1655546014873.png

 

 

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

MarcoWedel_2-1655546053178.png

 

hope this helps

Marco

View solution in original post

5 Replies
cr7dav
Contributor II
Contributor II
Author

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

Hi,

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

MarcoWedel_1-1655546014873.png

 

 

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

MarcoWedel_2-1655546053178.png

 

hope this helps

Marco

cr7dav
Contributor II
Contributor II
Author

This works!

Thank you very much, you are really kind

cr7dav
Contributor II
Contributor II
Author

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