Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

haribabugv
New Contributor III

BOM Table rearranging in qlikview

Hello Qlik Users,

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

This is my input

LevelComponentQty
050011
160010.5
270011
160021
270021
050021
160030.5
160041
270021
270041

 

I am expecting output something like this below

LevelParentComponentQty
0 50011
1500160010.5
2600170011
1500160021
2600270021
0 50021
1500260030.5
1500260041
2600470021
2600470041

 

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

Labels (2)
1 Solution

Accepted Solutions

Re: BOM Table rearranging in qlikview

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;

image.png

8 Replies
Highlighted
MVP
MVP

Re: BOM Table rearranging in qlikview

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.

Capture.JPG

Re: BOM Table rearranging in qlikview

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;

image.png

haribabugv
New Contributor III

Re: BOM Table rearranging in qlikview

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:

ParentComponentQty
500160010.5
600170011
500160021
600270021
500260030.5
500260041
600470021
600470041

 

OUTPUT

LevelComponentQty
050011
160010.5
270011
160021
270021
050021
160030.5
160041
270021
270041
haribabugv
New Contributor III

Re: BOM Table rearranging in qlikview

Hello Tresco,

If you see rowno 4 ,parent should be 5001.

 

 

Re: BOM Table rearranging in qlikview

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
New Contributor III

Re: BOM Table rearranging in qlikview

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

Re: BOM Table rearranging in qlikview

I am not sure I understand the logic here? Can you be able to elaborate?
haribabugv
New Contributor III

Re: BOM Table rearranging in qlikview

 

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:

ParentComponentQty
500160010.5
600170011
500160021
600270021
500260030.5
500260041
600470021
600470041

 

EXPECTED OUTPUT

LevelComponentQty
050011
160010.5
270011
160021
270021
050021
160030.5
160041
270021
270041
Community Browser