Output
Input
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\dshakir\AppData\Local\Temp\Engine_19340_47c765463ca14dbcbe69a646bdf62ba7_\Engine_3368_9e555a9b222c427faf40cddc603b8891_.yxdb
Single
Profile
C:\Users\dshakir\AppData\Local\Temp\Engine_19340_47c765463ca14dbcbe69a646bdf62ba7_\Engine_3368_1c4f4068ce5946c8a3b29c9afc786ab0_.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
[Line ID] >= [Source_Line ID] AND [Level] >= [Source_Level]
Custom
=
True
fixed
2020-02-24 13:52:12
0
2020-02-24 13:52:12
2020-02-24 13:52:12
[Line ID] >= [Source_Line ID] AND [Level] >= [Source_Level]
Line ID - Ascending
Source_Level - Ascending
Source_Line ID - Descending
Unique: Line ID, Source_Level
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...
Warning
All
ByName
Line ID - Ascending
Horizontal
challenge_81_dshakir