BOM Table rearranging in qlikview

[haribabugv]

Hello Qlik Users,

I am trying to rearrange BOM table by identifying each component's parent field.

This is my input

Level	Component	Qty
0	5001	1
1	6001	0.5
2	7001	1
1	6002	1
2	7002	1
0	5002	1
1	6003	0.5
1	6004	1
2	7002	1
2	7004	1

I am expecting output something like this below

Level	Parent	Component	Qty
0		5001	1
1	5001	6001	0.5
2	6001	7001	1
1	5001	6002	1
2	6002	7002	1
0		5002	1
1	5002	6003	0.5
1	5002	6004	1
2	6004	7002	1
2	6004	7004	1

I tried in using peek function but i was able to succeed partially.

Can you please check the below script and let me know what went wrong?

if(Level=0,'',if(Level=(peek(Level)+1),Peek(Component),Component)) as Parent

Thanks for your support

[tresesco]

Your code seems to be okay. Why do you think that there is an issue? May be you are getting confused with the tabular data at the front-end; your sort order is not helping you. You can check the data at the table preview or add one additional column using rowno() at the script and use that to sort data in the UI.

[sunny_talwar · Accepted solution]

Try this

Input:
LOAD RecNo() as Num1,
	 Level,
	 Component,
	 Qty,
	 If(Level = 0, Component, Peek('Level0Comp')) as Level0Comp,
	 If(Level = 1, Component, Peek('Level1Comp')) as Level1Comp,
	 If(Level = 2, Component, Peek('Level1Comp')) as Level2Comp,
	 Pick(Level, If(Level = 0, Component, Peek('Level0Comp')), If(Level = 1, Component, Peek('Level1Comp'))) as Parent	 	
FROM BOM.xlsx
(ooxml, embedded labels, table is Input);

DROP Fields Level0Comp, Level1Comp, Level2Comp;

[haribabugv]

Hello Sunny,

Thanks for your solution. It worked like a charm.

Now how to do it reverse? 

Input:

500 series is always level 0 from there i need to arrive at level 1,level 2 as shown in the output table

INPUT:

Parent	Component	Qty
5001	6001	0.5
6001	7001	1
5001	6002	1
6002	7002	1
5002	6003	0.5
5002	6004	1
6004	7002	1
6004	7004	1

OUTPUT

Level	Component	Qty
0	5001	1
1	6001	0.5
2	7001	1
1	6002	1
2	7002	1
0	5002	1
1	6003	0.5
1	6004	1
2	7002	1
2	7004	1

[haribabugv]

Hello Tresco,

If you see rowno 4 ,parent should be 5001.

[sunny_talwar]

So, the Level field is no longer available now? and you wish to create it? How do the number of rows increase from 8 to 10?

[haribabugv]

5001,5002 is now added in the same column . Hence number of rows increased from 8 to 10. When SKU starts with 5 series it is always level 0

[sunny_talwar]

I am not sure I understand the logic here? Can you be able to elaborate?

[haribabugv]

In the below example based on my input, i can see if Parent has 5*** then it is level 0, from level 0, we can find the child.

so in the first row, 5001  is level 0, 6001 is level 1 based on 1st row. 

in the second row,7001 is level 2 since it is child of 6001

in the third row, 6002 is level 1 since it is parent is 5001 which is level 0 so on.

Hope i was able to explain it 

INPUT:

Parent	Component	Qty
5001	6001	0.5
6001	7001	1
5001	6002	1
6002	7002	1
5002	6003	0.5
5002	6004	1
6004	7002	1
6004	7004	1

EXPECTED OUTPUT

Level	Component	Qty
0	5001	1
1	6001	0.5
2	7001	1
1	6002	1
2	7002	1
0	5002	1
1	6003	0.5
1	6004	1
2	7002	1
2	7004	1