| item | raw_material | usage |
| FG1 | raw1 | 4 |
| FG1 | raw2 | 1 |
| FG1 | semi1 | 6 |
| FG2 | semi2 | 10 |
| FG2 | raw2 | 5 |
| semi1 | raw3 | 7 |
| semi2 | raw4 | 10 |
I have a table about the bill of materials, as shown above,
for item FG1, it uses raw1 4pcs, raw2 1pcs, raw3 7pcs, semi1 6pcs,
and semi1 uses raw3 7pcs, so FG1 uses raw3 6*7=42pcs, and raw3 should be at level2,
for item FG2, it uses raw2 5pcs, semi2 10pcs,
and semi2 uses raw4 10pcs, therefore the FG2 uses raw4 10*10=100pcs. and raw4 should be at level2
I want to make a new table as shown in below picture, to re-organize the BOM table,
firstly define each item,
FG: finished goods, FG1, FG2,
M: manufactured, semi1, semi2,
P:purchase, the other raw materials,
| item | raw_material | type_of_raw_material | usage | level |
| FG1 | raw1 | P | 4 | 1 |
| FG1 | raw2 | P | 1 | 1 |
| FG1 | semi1 | M | 6 | 1 |
| FG1 | raw3 | P | 42 | 2 |
| FG2 | raw2 | P | 5 | 1 |
| FG2 | semi2 | M | 10 | 1 |
| FG2 | raw4 | P | 100 | 2 |
this is just simple example, in my situation, the level reaches even bigger such as level 5, or level 6, a recursion is must, but I don't know how to deal with it using Qlik sense.
I need your help, thanks advanced,
my scripts as below, the recursion part is blank:
BOM:
load * inline [
Parent,son,qty
FG1,raw1,4
FG1,raw2,1
FG1,semi1,6
FG2,semi2,10
FG2,raw2,5
semi1,raw3,7
semi2,raw4,10
];
//STEP1, define FG, M, AND P, create the item list maste table
ItemList:
load Parent as item,If(not Exists(son,Parent),'FG','M') as type Resident BOM;
Concatenate(ItemList)
load son as item, If(not Exists(Parent, son),'P','M') as type Resident BOM
where not Exists(item, son);
step2, recursion
//sub call
sub expand_BOM(item,level,qty)
Temp:
load Parent as subParent,son as subSon,qty*$(qty) as subQty Resident BOM where Parent=$(item);
//have no idea how
end sub
//main loop
for each fgItem in FieldValueList('Parent')
temp:
NoConcatenate load FGNo as tempFG, rawItem as tempItem,type,qty Resident FGBOM WHERE FGNo='$(fgItem)';
for each tempRow in FieldValueList('tempItem')
//have no idea
next
next
does anyone can provide some useful information or solution for this, thanks
