Output
Q1. Which Store and Item have the most unmet supply?
Q2. Which Item had the most units allocated across the retail network?
Q3. Based on cumulative distance traveled (# items assign to location multiplied by the distance traveled), which items collectively traveled less than 400,000 miles?
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==
Weekly Challenge #48
Use Case:
There are 3 warehouses across the US that supply 26 retail locations. The retailers have products/items that are in high demand and, as a result, out of supply within their stores.
Your job as a distribution manager is to allocate as much product from the warehouses as possible, but there are a few constraints:
1. A retailer location can only be supplied by the nearest warehouse.
2. Prioritization of product allocation across retailers is dependent on required product/item need at the location. For example higher priority is the where there is the highest required.
Goal:
Allocate as much product from the 3 warehouses as possible to the 26 retail locations
X
9
68
7
1114
4
343391.9
6
384310.3
9
303567.3
10
385761.6
C:\Temp\Engine_20396_1b00c97d245446f0ad7a3c319f090889_\Engine_1128_c6f45ec36f874cbe81ccd45e9162280e_.yxdb
Single
Profile
C:\Temp\Engine_20396_1b00c97d245446f0ad7a3c319f090889_\Engine_1128_a6a439fc4b8746798d4a5bfb2ecde5d4_.yxdb
Single
Profile
C:\Temp\Engine_20396_1b00c97d245446f0ad7a3c319f090889_\Engine_1128_3fe39945858347f39b328760a795715c_.yxdb
Single
Profile
A
Houston
TX
-95.562207
29.839187
B
Birmingham
AL
-86.625591
33.410321
C
Seattle
WA
-122.291399
47.603993
D
Detroit
MI
-83.255088
42.412144
E
Nashville
TN
-86.729174
36.135464
F
Chicago
IL
-87.597287
41.734
G
Lansing
IA
-91.228717
43.361018
H
Fayetteville
NC
-78.907738
35.024302
I
Miami
FL
-80.31047
25.792304
J
Tampa
FL
-82.539427
27.886595
K
Charleston
SC
-80.063175
32.931254
L
Los Angeles
CA
-118.386826
33.945722
M
Sacramento
CA
-121.40379
38.568865
N
Tucson
AZ
-110.845502
32.311728
O
Dallas
TX
-96.770293
32.84673
P
Austin
TX
-97.713678
30.19727
Q
Tulsa
OK
-95.861086
36.177745
R
New York
NY
-74.002527
40.734408
S
Baltimore
MD
-76.467296
39.3277
T
Portland
OR
-122.889658
45.563461
U
Philadelphia
PA
-75.171584
39.955317
V
Boston
MA
-71.066428
42.315352
W
Helena
MT
-112.00339
46.593143
X
Denver
CO
-104.943955
39.709101
Y
Milwaukee
WI
-87.925026
42.96156
Z
Las Vegas
NV
-115.242869
36.177034
Retail Location
Main
1
824
Main
2
1145
Main
3
1354
Main
4
916
Main
5
1120
Main
6
635
Main
7
1056
Main
8
824
Main
9
616
Main
10
679
Second
1
432
Second
2
589
Second
3
111
Second
4
123
Second
5
954
Second
6
376
Second
7
1124
Second
8
260
Second
9
88
Second
10
700
Third
1
145
Third
2
998
Third
3
1037
Third
4
34
Third
5
45
Third
6
732
Third
7
856
Third
8
621
Third
9
21
Third
10
1345
Warehouse Supply
Main
Huntsville
AL
34.737177
-86.603266
Second
El Paso
TX
31.767002
-106.492058
Third
Colorado Springs
CO
38.825775
-104.831478
Warehouse Locations
A
1
47
A
2
29
A
3
50
A
4
12
A
5
19
A
6
72
A
7
71
A
8
2
A
10
40
B
2
46
B
3
36
B
4
7
B
5
48
B
6
71
B
7
19
B
10
4
C
1
34
C
2
52
C
3
66
C
4
17
C
6
3
C
7
57
C
8
77
C
10
13
D
2
1
D
3
76
D
4
46
D
6
21
D
7
40
D
10
41
E
1
44
E
2
14
E
5
75
E
6
32
E
7
14
E
8
76
E
10
42
F
1
71
F
2
28
F
3
46
F
4
54
F
5
9
F
6
52
F
7
43
F
8
33
F
9
59
F
10
16
G
1
60
G
2
2
G
3
51
G
4
66
G
5
47
G
6
28
G
7
66
G
9
30
G
10
55
H
1
11
H
2
69
H
3
5
H
4
41
H
5
54
H
7
28
H
8
57
H
9
17
H
10
36
I
2
6
I
4
73
I
5
43
I
6
3
I
7
70
I
8
70
I
9
16
I
10
17
J
1
71
J
2
35
J
4
5
J
5
79
J
6
9
J
7
38
J
9
55
J
10
72
K
1
44
K
2
13
K
3
63
K
4
73
K
6
62
K
7
61
K
8
20
K
10
25
L
1
26
L
3
10
L
4
35
L
5
62
L
6
16
L
7
73
L
8
35
L
9
78
L
10
33
M
2
12
M
3
23
M
4
55
M
5
46
M
6
9
M
7
4
M
8
8
M
9
76
M
10
60
N
2
9
N
4
29
N
5
12
N
6
4
N
7
36
N
8
3
N
10
8
O
1
15
O
2
35
O
5
30
O
6
43
O
8
78
O
9
48
O
10
64
P
2
60
P
3
33
P
4
5
P
5
9
P
6
27
P
7
62
P
8
71
P
9
30
P
10
50
Q
2
22
Q
3
32
Q
4
11
Q
5
4
Q
6
46
Q
7
69
Q
8
46
Q
9
12
Q
10
53
R
2
76
R
3
15
R
4
13
R
5
69
R
6
41
R
7
49
R
8
50
R
10
9
S
1
23
S
2
9
S
4
27
S
5
31
S
6
31
S
7
17
S
8
69
S
9
53
S
10
21
T
1
19
T
2
66
T
3
76
T
4
56
T
5
78
T
6
53
T
7
62
T
8
2
T
9
12
U
1
71
U
2
54
U
4
3
U
5
54
U
6
3
U
7
48
U
8
6
V
1
65
V
2
30
V
3
4
V
4
12
V
5
50
V
7
37
V
8
10
V
9
76
W
1
76
W
2
68
W
3
10
W
4
48
W
5
65
W
7
60
W
8
15
W
9
31
X
1
8
X
2
49
X
3
57
X
4
23
X
5
25
X
7
39
X
8
45
X
9
68
Y
1
6
Y
2
47
Y
4
1
Y
5
26
Y
6
64
Y
7
33
Y
8
41
Y
9
33
Y
10
7
Z
1
60
Z
2
25
Z
3
39
Z
4
45
Z
5
75
Z
6
18
Z
7
18
Z
8
25
Z
9
2
Z
10
10
Retail Demand
C:\Temp\Engine_20396_1b00c97d245446f0ad7a3c319f090889_\Engine_1128_719d2823ee5b40748b0000132bc6fc4e_.yxdb
Single
Profile
C:\Temp\Engine_20396_1b00c97d245446f0ad7a3c319f090889_\Engine_1128_68db19b3ef4e4fd8be22e1c3f2629991_.yxdb
Single
Profile
C:\Temp\Engine_20396_1b00c97d245446f0ad7a3c319f090889_\Engine_1128_9bd14dd339b04598b805e7f97c9c9d12_.yxdb
Single
Profile
Double
Double
Miles
Latest
Item - Ascending
Warehouse - Ascending
Priority - Ascending
Store - Ascending
Item
Remaining Stock
Int32
4
NULL
if [Row-1:Remaining Stock]==null() then [WH_Item_Count]-required else
[Row-1:Remaining Stock]-[Required] endif
remaining stock
if [Row-1:Remaining Stock]==null() then [WH_Item_Count]-requi...
Item
Unmet Demand
Int32
4
Empty
if [Remaining Stock]>=0 then 0 else
if [Row-1:Remaining Stock]>=0 then
abs([Remaining Stock]) else required
endif endif
unmet demand
if [Remaining Stock]>=0 then 0 else
if [Row-1:Remaining Stoc...
Unmet Demand - Descending
C:\Temp\Engine_20396_1b00c97d245446f0ad7a3c319f090889_\Engine_1128_e8e10082777f4b0a8cd6ff3f249f34c6_.yxdb
Single
Profile
Item - Ascending
Required - Descending
Item
Priority
Int32
4
Empty
[Row-1:Priority]+1
add priority
[Row-1:Priority]+1
First
1
First 1
# of fulfilled and total miles
fulfilled = if [Unmet Demand]==0 then [Required]
else if [Unmet Demand]=ABS([Rem...
Sum_fulfilled - Descending
First
1
First 1
C:\Temp\Engine_20396_1b00c97d245446f0ad7a3c319f090889_\Engine_1128_d254981c12974e2d821d2bd04d4e635b_.yxdb
Single
Profile
Simple
Sum_Total_Miles
<
400000
[Sum_Total_Miles] < 400000
C:\Temp\Engine_20396_1b00c97d245446f0ad7a3c319f090889_\Engine_1128_7b0fc8e1dd6847c2ae0cf6d841fa2cfd_.yxdb
Single
Profile
Horizontal
challenge_48_MS