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:\Users\pasccout\AppData\Local\Temp\1\Engine_12740_7f640fbe6ee9420fb39b7bc6990d6e21_\Engine_6044_777504004d904ac591e329cd2882a3cd_.yxdb
Single
Profile
C:\Users\pasccout\AppData\Local\Temp\1\Engine_12740_7f640fbe6ee9420fb39b7bc6990d6e21_\Engine_6044_ada9a090d4084c35bd152d0991b941b7_.yxdb
Single
Profile
C:\Users\pasccout\AppData\Local\Temp\1\Engine_12740_7f640fbe6ee9420fb39b7bc6990d6e21_\Engine_6044_0ae7dfd317b34f7896646602d140e286_.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:\Users\pasccout\AppData\Local\Temp\1\Engine_12740_7f640fbe6ee9420fb39b7bc6990d6e21_\Engine_6044_4840f9e66be34679b6d5b53bdec5db24_.yxdb
Single
Profile
C:\Users\pasccout\AppData\Local\Temp\1\Engine_12740_7f640fbe6ee9420fb39b7bc6990d6e21_\Engine_6044_54d841c8f3cf47c581ecb4d85e745d13_.yxdb
Single
Profile
C:\Users\pasccout\AppData\Local\Temp\1\Engine_12740_7f640fbe6ee9420fb39b7bc6990d6e21_\Engine_6044_df45d7450d544ec4b0b221f571520b99_.yxdb
Single
Profile
Get the location for the stores
Double
Create the spatial location of the store
Double
Create the location of the warehouse
Error
Append the possible warehouses to the stores
Store Location
Warehouse location
Latest
Miles
Get the distance by store
Store - Ascending
DistanceMiles - Ascending
Keep the closest warehouse
Unique: Store, Item
Sort the items with the required count descending to set the priority
Warehouse - Ascending
Item - Ascending
Required - Descending
Get the availability of items by warehouse
Store
Available Left
Int32
4
Empty
If [Row-1:Item] = [Item] And
[Row-1:Warehouse] = [Warehouse] Then
If [Row-1:Available Left] >= [Required] Then
[Row-1:Available Left] - [Required]
Else
0
EndIf
Else
If [Available Count] >= [Required] Then
[Available Count] - [Required]
Else
[Available Count]
EndIf
EndIf
Calculate how many items are left after distribution
If [Row-1:Item] = [Item] And
[Row-1:Warehouse] = [Warehou...
Store
fulfilled
Int32
4
Empty
If [Row-1:Item] = [Item] And
[Row-1:Warehouse] = [Warehouse] Then
If [Available Left] > [Required] Then
[Required]
Else
If [Available Count] > [Row-1:fulfilled] And
[Row-1:Available Left] > 0 Then
[Available Count] - [Row-1:fulfilled]
Else
[Available Left]
EndIf
EndIf
Else
If [Available Count] > [Required] Then
[Required]
Else
[Available Count]
EndIf
EndIf
Determine the number of items fulfilled
If [Row-1:Item] = [Item] And
[Row-1:Warehouse] = [Warehous...
Determine the number of unmet items
Unmet - Descending
Store - Ascending
Item - Ascending
Select required fields
C:\Users\pasccout\AppData\Local\Temp\1\Engine_12740_7f640fbe6ee9420fb39b7bc6990d6e21_\Engine_6044_ea2655e5d9a949cfa88a8195f5c7d97d_.yxdb
Single
Profile
Question #1
First
1
First 1
Sum_fulfilled - Descending
First
1
First 1
C:\Users\pasccout\AppData\Local\Temp\1\Engine_12740_7f640fbe6ee9420fb39b7bc6990d6e21_\Engine_6044_c05f7f28d06b499a80685ada0bb482b3_.yxdb
Single
Profile
Question #2
[Sum_Total Miles] <= 400000
Simple
<=
Sum_Total Miles
True
fixed
2018-11-07 08:59:34
0
400000
2018-11-07 08:59:34
2018-11-07 08:59:34
[Sum_Total Miles] <= 400000
Question #3
Horizontal
challenge_48_my_solution