iVBORw0KGgoAAAANSUhEUgAAAXoAAAA0CAIAAAAL52RvAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAAEnEAABJxAfPceo4AAAASdEVYdFNvZnR3YXJlAEdyZWVuc2hvdF5VCAUAAA0cSURBVHhe7Z3fjhXHEYf9Bs4bxG+A38B+A/sN7DdI3sBccYcSKXBhCQkuLPliibGEfcNeJGKvFiFhNgoKijFai+BVEPJCIrAlS+TbU72V2uo/02dn5uyedX1qjU7XVPd0D1O/qZ4zZ3nrTRAEwUoIuQmCYEWE3ARBsCJORm7+8uQ/Nx7tp0qwDjx58uTu3bu7u7upHgTLs2q5efzi5/e/+PatP31DSaZgHQi5CcazUrm59+zVbz79m2jNsnLz40+/nN/eQ6oQrGQKVkjITTCelcrNJ9s/qNYsJTdWp1iIJWuwQkJugvGsh9y8/emOtgq5ORFCboLxrIfc2FYhNydCyE0wnpCboIuQm2A8ITdBFyE3wXhCboIuQm6C8YTcBF2E3ATjGZCbH3/65Y/fPPt48/v3v/jWvjJDeffzh9jZe+/Zq+Q9xMnKDeM8v733+9v/Yi5s+fzloxdpXzBEyE0wngG5+eCr72yo18o71x4QwIMv4C0lN3+49290gYKu2VZoHEphy6AAXX3w3GmlFuz0gKom1xLs/euT/0pJpsXvMES5RLySdQG71L/ds6JNrHbzWftJpgp2hDO9BhlyE4xnQG4+2tx18dkohG5qVmEpubGe7fLen/+Z2mQQh06tigWfhi5YzUUE8URi1CIluS6wdk5gstZBDdX/t9f+nqxH3zb63e0nyZrh5thzxGMQchOMZ0BuuOty0RPPhBxiYQsWGw9S2okGraxzslawnu3SkJtzn/8jd5bi7ERsapNhnZl1nii5AVh5GpRgsIPkFCXrIr9TO6V2blEi67bW2c3+/j6HAD4kU5PXr1+LPyRTnU43RQbDIVJ9wbKd7O3tuU7EwjbVSxR9BluBjNmdPSzWKNUexH9Cxj4qdgoyodzkSlErNkQt7nBUbQrDZxeotX5ybXLFJRQ3Hu3bve3fvqMO1tmJhT0JrFjzFIwTrg6U2hTGM6vcEAk3b968fPnyBQPVzc1NF+0Cxq2trStXriTXQ7Ds7OwkpwzxuXXrVqo3efjwofi7KYuR0aZ6k9u3b4u/7eSzzz7DwjbVSxR9Fj1duH//fqqXkCOyTfUF0lCNUu1BTsLFixcbMscucb5z504y1RkrN+5yn1BulKUOobgwJlNIO45iFYd4Ttaj5HJDWofEMJJ7z16xzVWANZE6t1c3NoVxWRLQv+6luCUVx2XMuhdtSjtmYD65IX7kei3Ctc5Fn1wXcH1jTLsXEJbWgugUwyPtvnBhMEeAS5cuiXNRbmDwVKCJOqoJ5aYd/NPKDc6Dox10sJxZubE6Yh+IOIhYdaMUO3dyQ1TbB7pFXN6U65Fihenqg+fJanAnzY7QHWVwVGOYSW64JS4u7AOIBxtIHIskAvvGxkYyHdUa9lp/UiR6kL3FmJRWMBgbVgFrcjPYiUS+MKHcAHpaTPpADqrKIkgrZ1QYmzgU/3E5q7K3mFXpiWoooOXMyo19rlRLbQSrJowwWQ3WgW57otrlVkUdAZu80HNNlYpLKndaiiOfkDnkhmtULtbGHRu7DS1dQLmUR8FfFAfPZDoEo0pVYyKalUiC4zwXrVM/jXVNo5ORciPd1lZzk8sNSJ+sbZ3G6Rw716dwNuXm2KP64KvvktVg5SZf79SwGlHsFmx60lhzuenIksr2T4rUSKAmYQ65kaCCznuj3ktrkSNo/Dg5ECOxwZbgSdYMiS7cZHhuygddLAbAttGJ5GVs806KUuIo+mABPQlFsZOBufMj/rWTpqer8Y8roul6kGOhOE6GGpxNuXHf6SRrBTuqopocT27s19uUohzYFKw9L7du+vDrx7bac05GMrncaJZeu1HnSGrTc303wlXvycVwlb1yCOmkKDfaSTGGZWo48CHvpDg2R9EHC/BBtAxymRYJcKMS5+JQoUdu1IcZiUX/+WppZpGzKTdkCrZJslaYSW7QF21FyddT9gusxtMlgd7sUx5bJNmZm8nlRu/SndcrES7+PfKkj4RSfYFaGrdliWSJzFwp4KCLRScy/mN0UpQSR9EHC/CBI4ry5gscmZpTlkW7UXIDGxsb+Fy/fl2qPbPI6ZIbrvUvH704v73HTVXeo9XiXqI7JXJjBYLixuyK/XJnQrkBq3ocKFkPsXt7JMOdBykrWEYJk8uNBAZ0rqQ0KmphYymGkFjkc3F1IHdsdkkMS0S5KR90cbQT9+RCU5taJz2BWvTBAvJZH1Fp/AtyVt28Fu3Gyg3zkiPipk002elkQG4ev/j5483v3SXeKKdTbvrLtHLjXsCx79S43Me9blPDLakoK/sPLeaTm1QfQi/xWthY1NkOWCzyWXMTGzByA9dFVq4UcNDFYSd6FNuJtNKXUPJOilLiKPpggVSpPMmSs+pOUe5mKZ6rIpIzMioZXs8/hKMlN+T/tZ8a1coccuMegswqN8XntceWG7ArIPsFmZ3Uue73ZZzy9ryyPBXzyU3nHVKjoucqL4aQWFLlMKR1aSZNSFikCuLgpnzQRdaJphg9nYiFbaqXKPpggVRZII+9QfuXs+pOkfjUzpuMGdxMi+g3g5oDLkVVbrjfOq0hKtALCndUrnst1odqal/ieHLjWrUPIViBYNg61MGS2h9ljNzYwdvfSdg+88c6NZiLtpJC/2nfzMwnN519snwQ/x65kddhwWqZWFLFhJms5mq64Ia3aPH/TnRU4iZNND+CWrdsU71E0QcLpMoh7vH53HKjJ3apJ8RKVW7sD3/aL5uoG6UWscKJyM34FGCM3LgXcGTR5IydD1/cedAy69t9yuRyoxduj3wI8qwkf6EmR277NsuAxdGOhKusnohqWZi48JaYd1M+6OJoJ/JgGGeJ28FO9KCpXkJaOR8skCqHoKfySEVOy9xys5RzTlVu7AXdfk3Oep5CuaEk63EZIzdgm8uZtN/TN163sdgXAl1p/Lh0QiaXG5A4aby94pDAHrzWuc9Lz7pQEqRtqiwgVsUo/u6hda4UsHD3nehE2Dr/vBNRBJqkegkR1qJqpIpBhRuRXUu5IaTtBd2O8H7PlcmNazLyu5uRcmMf08jPsuyaqPNZr21CsuleLGK+yW825pAbiQ2oRYJD1YE7eePBgaqSXUmBGFPlEH384bQJOuUGdCIuH4G8E11/2TWXRUPayZ8YU+UoOgtZW6233CRrBeu5lNwcbxHRIzdu/P0PR4qMlBumaV/ns+oz+LqN4MRFUiQ7KsrcS6o55EbTEKj9nphAskKggU1cOTUBOrx+/bo45B2KPVUOkTFA3lu/3CzbiRjxz88nEoOdvblyHRy4IjcgfQrrLTftCO/3PIZwgAu2nnTAfc2cv/OyFCPlBtxrh1p6Xrdxz+x1ANitis29pJpDbkCjC1iMoBEcQuCzLE9cGGvyAojL1tYWqwm22LWrPFUB2ZUqBsQCUsXQLzewVCdMR4fKFHZ2dhYz3t3c3BQje11qA7IrVTI4uva5ZnLjnhQ0sgP31LOtIPbGTul8bOG0r7OVi/AxCc54uXFT0CJPjtvYo6Mvtkkx65mJmeQGiCtZAhQhXPPAYxkiTzdysNcWKeKQKh0sJTc1ip1AY9ZMIZ8yyN5UKaFasGZyA/bO+c61B3lgIEn5G4DcY+XvB5NQ5BGeP+/EU3omH5EXl/NFgVM0iv5dZLb9rXArLt8ahxbGyw3kP0E41/G6jROU/BmNHRtJkJyWOZhPbgQyFFISgpNbNPHGB5ZR7cPRhBDa2NjAmS2fsaR9JXCDVOmAAeDvIn+SThSUkZGL7jBrPte0EnoOzRnIO5GGtZ4ZmzjUBmlZyjmnKjf5C6woiPwHBh9+/di++F8rxW+gaz/80TL4m+xiKS4l3NpNCp4yEflBhp1IbT0yidzkgxlMuNAOu4wqypNT1ZHLxgZzy03wa6AqN9zzB6WBgk9DC/J8obas0FIUqTwtyktyPUrtoUmtFNOfSeQmz7aKx7K4E1tLvpyQzbSkCrkJxlOVG+D6zl9jtYUMiJghkOzKS4t70KBwVy/6S6n9aZgbj/YbrRoLkzxNa5TigCeRG7D9DD6Bcs+50JS0o4T9Z5ppSRVyE4ynJTcC1z0SoJkOMU/YcAu1N2c+Ez/qw9VPkDcuevzpgW7pSjqkEFG1G7hQbMWBBr+rYiQMr6FWjLwxYAYmbdkOroAaMHI94uCYmalMUOaYrBU4b+pMaZ/G4xFyE4xnWG7OEsQhcY58SCGkWdzNkQvkcBTVGmQrWdeHkJtgPL8uuTlB7JpuMFs5hYTcBOMJuVkFLAPtd0yryaemJeQmGE/IzSpg4aZa896Ih80nSMhNMJ6Qm9m59+yVTW3aL16fWkJugvGE3MwIa6jz23tWa9Y0tYGQm2A8ITcTg6AgK+8e/YvxUt6u/8d1p5+Qm2A8ITcT4yRGC1ozx+swKyPkJhhPyM3EOJWR8tHm7vrmNULITTCekJuJ0fee2X6y/cPVB8/X8WvvnJcvXz59+jT/81FB0E/ITRAEKyLkJgiCFRFyEwTBSnjz5n+Bsg0wC+hiuAAAAABJRU5ErkJggg==
Challenge #81
Multi Level Bill of Material
A bill of materials or product structure (sometimes bill of material, BOM or associated list) is a list of the raw materials, sub-assemblies, intermediate assemblies, sub-components, parts and the quantities of each needed to manufacture an end product.
Given a Bill of Materials, where each record is a line item with a Line ID, Level, Part Number, Description, Unit, Quantity, and Parent ID.
Calculate the Full Quantity and Parent Quantity of each line item.
For example, 1 car has 4 wheels, each wheel has 5 bolt assemblies, and each bolt assembly has 2 washers. For the washer line item, the Full Quantity is 40, and the Parent Quantity is 20.
The ideal solution would work for any number of levels.
C:\Users\johne\AppData\Local\Temp\Engine_10960_5b5083e6886947a38133c85e37acf43d_\Engine_52952_34ac87a2560241d49059f37058cfc140_.yxdb
Single
Profile
1
1
101-101
Car
EA
1
2
2
102-102
Wheel
EA
4
1
3
3
103-103
Bolt Assembly
EA
5
2
4
4
104-104
Washer
EA
2
3
5
1
120-001
Trolley, 3 wheeled
EA
1
6
2
112-001
Plywood Platform
EA
1
5
7
3
106-001
Plywood,12mm,2400x1200
SH
0.125
6
8
3
111-006
Varnish, Semi Gloss
L
0.05
6
9
3
130-001
Labor
HR
0.65
6
10
2
110-001
Wheel Housing
EA
3
5
11
3
110-003
Side Piece
EA
2
10
12
4
105-001
MS Flat 80x8
M
0.1
11
13
4
111-001
Galvanising
KG
0.001
11
14
4
130-000
Labor
HR
0.1
11
15
3
110-002
Top Piece
EA
1
10
16
4
105-001
MS Flat 80x8
M
0.05
15
17
4
111-001
Galvanising
KG
0.001
15
18
4
130-000
Labor
HR
0.5
15
19
3
102-108
Wheel, with tyre, 100mm
EA
1
10
20
3
100-001
MS Bolt, M10x70, Galv
EA
1
10
21
3
100-002
M10, washer, Galv
EA
2
10
22
3
100-003
M10, Nut, Galv
EA
3
10
23
3
100-004
MS Bolt, M10x30, Galv
EA
1
10
24
3
100-005
M10 Square Nut
EA
1
10
25
1
16844-23003
Field Adapter
EA
1
26
2
15476-62009
Control
EA
1
25
27
2
16763-87001
Moisture Tester
EA
2
25
28
3
18701-19302
Enclosure
EA
4
27
29
3
12000-14273
Machine Screw
EA
4
27
30
3
09000-16001
Encapsulant
EA
1
27
31
2
14644-38389
Precipitator Assy.
EA
2
25
32
3
52010-48007
Element
EA
2
31
33
3
57002-55100
Housing
EA
2
31
34
3
98002-66600
Machine Screw
EA
8
31
35
3
12000-14267
Precipitator
EA
2
31
1
1
101-101
Car
EA
1
1
2
2
102-102
Wheel
EA
4
1
4
1
3
3
103-103
Bolt Assembly
EA
5
2
20
4
4
4
104-104
Washer
EA
2
3
40
20
5
1
120-001
Trolley, 3 wheeled
EA
1
1
6
2
112-001
Plywood Platform
EA
1
5
1
1
7
3
106-001
Plywood,12mm,2400x1200
SH
0.125
6
0.125
1
8
3
111-006
Varnish, Semi Gloss
L
0.05
6
0.05
1
9
3
130-001
Labor
HR
0.65
6
0.65
1
10
2
110-001
Wheel Housing
EA
3
5
3
1
11
3
110-003
Side Piece
EA
2
10
6
3
12
4
105-001
MS Flat 80x8
M
0.1
11
0.6
6
13
4
111-001
Galvanising
KG
0.001
11
0.006
6
14
4
130-000
Labor
HR
0.1
11
0.6
6
15
3
110-002
Top Piece
EA
1
10
3
3
16
4
105-001
MS Flat 80x8
M
0.05
15
0.15
3
17
4
111-001
Galvanising
KG
0.001
15
0.003
3
18
4
130-000
Labor
HR
0.5
15
1.5
3
19
3
102-108
Wheel, with tyre, 100mm
EA
1
10
3
3
20
3
100-001
MS Bolt, M10x70, Galv
EA
1
10
3
3
21
3
100-002
M10, washer, Galv
EA
2
10
6
3
22
3
100-003
M10, Nut, Galv
EA
3
10
9
3
23
3
100-004
MS Bolt, M10x30, Galv
EA
1
10
3
3
24
3
100-005
M10 Square Nut
EA
1
10
3
3
25
1
16844-23003
Field Adapter
EA
1
1
26
2
15476-62009
Control
EA
1
25
1
1
27
2
16763-87001
Moisture Tester
EA
2
25
2
1
28
3
18701-19302
Enclosure
EA
4
27
8
2
29
3
12000-14273
Machine Screw
EA
4
27
8
2
30
3
09000-16001
Encapsulant
EA
1
27
2
2
31
2
14644-38389
Precipitator Assy.
EA
2
25
2
1
32
3
52010-48007
Element
EA
2
31
4
2
33
3
57002-55100
Housing
EA
2
31
4
2
34
3
98002-66600
Machine Screw
EA
8
31
16
2
35
3
12000-14267
Precipitator
EA
2
31
4
2
Allow
C:\Users\johne\AppData\Local\Temp\Engine_10960_5b5083e6886947a38133c85e37acf43d_\Engine_52952_1c02da1f77fe46a6969aef2424568536_.yxdb
Single
Profile
[Line ID] >= [Source_Line ID] AND
[Level] >= [Source_Level]
Custom
[Line ID] >= [Source_Line ID] AND
[Level] >= [Source_Level]
Line ID - Ascending
Level - Ascending
Source_Line ID - Descending
Line ID
Multiple
Double
8
NULL
IF Isnull([Row-1:Source_Quantity]) THEN 1
ELSE [Row-1:Multiple]*[Source_Quantity]
ENDIF
IF Isnull([Row-1:Source_Quantity]) THEN 1
ELSE [Row-1:Multipl...
Group by line ID
and return last
Quantity calc for
each group
Group by line ID
and return last
Quantity calc for
each group
pull in parent
quantity by
joining line ID
to parent ID
pull in parent
quantity by
joining line ID
to parent ID
Warning
All
ByName
Union data
together
Union data
together
sort back to
original order
Line ID - Ascending
C:\Users\johne\AppData\Local\Temp\Engine_10960_5b5083e6886947a38133c85e37acf43d_\Engine_52952_1631cd0e6f9945209f6c71fae4dadc9e_.yxdb
Single
Profile
Horizontal
Challenge 81 Solution