Weekly Challenge #198
iVBORw0KGgoAAAANSUhEUgAAAXoAAAA0CAIAAAAL52RvAAAABGdBTUEAALGPC/xhBQAAAAlwSFlzAAAScQAAEnEB89x6jgAAABJ0RVh0U29mdHdhcmUAR3JlZW5zaG90XlUIBQAADRxJREFUeF7tnd+OFccRh/0GzhvEb4DfwH4D+w3sN0jewFxxhxIpcGEJCS4s+WKJsYR9w14kYq8WIWE2CgqKMVqL4FUQ8kIisCVL5NtTvZXa6j/TZ2fm7J51fWqNTtdU93QPU7+pnjNneetNEATBSgi5CYJgRYTcBEGwIk5Gbv7y5D83Hu2nSrAOPHny5O7du7u7u6keBMuzarl5/OLn97/49q0/fUNJpmAdCLkJxrNSubn37NVvPv2baM2ycvPjT7+c395DqhCsZApWSMhNMJ6Vys0n2z+o1iwlN1anWIgla7BCQm6C8ayH3Lz96Y62Crk5EUJugvGsh9zYViE3J0LITTCekJugi5CbYDwhN0EXITfBeEJugi5CboLxhNwEXYTcBOMZkJsff/rlj988+3jz+/e/+Na+MkN59/OH2Nl779mr5D3EycoN4zy/vff72/9iLmz5/OWjF2lfMETITTCeAbn54KvvbKjXyjvXHhDAgy/gLSU3f7j3b3SBgq7ZVmgcSmHLoABdffDcaaUW7PSAqibXEuz965P/Skmmxe8wRLlEvJJ1AbvUv92zok2sdvNZ+0mmCnaEM70GGXITjGdAbj7a3HXx2SiEbmpWYSm5sZ7t8t6f/5naZBCHTq2KBZ+GLljNRQTxRGLUIiW5LrB2TmCy1kEN1f+31/6erEffNvrd7SfJmuHm2HPEYxByE4xnQG6463LRE8+EHGJhCxYbD1LaiQatrHOyVrCe7dKQm3Of/yN3luLsRGxqk2GdmXWeKLkBWHkalGCwg+QUJesiv1M7pXZuUSLrttbZzf7+PocAPiRTk9evX4s/JFOdTjdFBsMhUn3Bsp3s7e25TsTCNtVLFH0GW4GM2Z09LNYo1R7Ef0LGPip2CjKh3ORKUSs2RC3ucFRtCsNnF6i1fnJtcsUlFDce7du97d++ow7W2YmFPQmsWPMUjBOuDpTaFMYzq9wQCTdv3rx8+fIFA9XNzU0X7QLGra2tK1euJNdDsOzs7CSnDPG5detWqjd5+PCh+Lspi5HRpnqT27dvi7/t5LPPPsPCNtVLFH0WPV24f/9+qpeQI7JN9QXSUI1S7UFOwsWLFxsyxy5xvnPnTjLVGSs37nKfUG6UpQ6huDAmU0g7jmIVh3hO1qPkckNah8QwknvPXrHNVYA1kTq3Vzc2hXFZEtC/7qW4JRXHZcy6F21KO2ZgPrkhfuR6LcK1zkWfXBdwfWNMuxcQltaC6BTDI+2+cGEwR4BLly6Jc1FuYPBUoIk6qgnlph3808oNzoOjHXSwnFm5sTpiH4g4iFh1oxQ7d3JDVNsHukVc3pTrkWKF6eqD58lqcCfNjtAdZXBUY5hJbrglLi7sA4gHG0gciyQC+8bGRjId1Rr2Wn9SJHqQvcWYlFYwGBtWAWtyM9iJRL4wodwAelpM+kAOqsoiSCtnVBibOBT/cTmrsreYVemJaiig5czKjX2uVEttBKsmjDBZDdaBbnui2uVWRR0Bm7zQc02Viksqd1qKI5+QOeSGa1Qu1sYdG7sNLV1AuZRHwV8UB89kOgSjSlVjIpqVSILjPBetUz+NdU2jk5FyI93WVnOTyw1In6xtncbpHDvXp3A25ebYo/rgq++S1WDlJl/v1LAaUewWbHrSWHO56ciSyvZPitRIoCZhDrmRoILOe6PeS2uRI2j8ODkQI7HBluBJ1gyJLtxkeG7KB10sBsC20YnkZWzzTopS4ij6YAE9CUWxk4G58yP+tZOmp6vxjyui6XqQY6E4ToYanE25cd/pJGsFO6qimhxPbuzX25SiHNgUrD0vt2768OvHttpzTkYyudxoll67UedIatNzfTfCVe/JxXCVvXII6aQoN9pJMYZlajjwIe+kODZH0QcL8EG0DHKZFglwoxLn4lChR27UhxmJRf/5amlmkbMpN2QKtkmyVphJbtAXbUXJ11P2C6zG0yWB3uxTHlsk2ZmbyeVG79Kd1ysRLv498qSPhFJ9gVoat2WJZInMXCngoItFJzL+Y3RSlBJH0QcL8IEjivLmCxyZmlOWRbtRcgMbGxv4XL9+Xao9s8jpkhuu9S8fvTi/vcdNVd6j1eJeojslcmMFguLG7Ir9cmdCuQGrehwoWQ+xe3skw50HKStYRgmTy40EBnSupDQqamFjKYaQWORzcXUgd2x2SQxLRLkpH3RxtBP35EJTm1onPYFa9MEC8lkfUWn8C3JW3bwW7cbKDfOSI+KmTTTZ6WRAbh6/+Pnjze/dJd4op1Nu+su0cuNewLHv1Ljcx71uU8MtqSgr+w8t5pObVB9CL/Fa2FjU2Q5YLPJZcxMbMHID10VWrhRw0MVhJ3oU24m00pdQ8k6KUuIo+mCBVKk8yZKz6k5R7mYpnqsikjMyKhlezz+EoyU35P+1nxrVyhxy4x6CzCo3xee1x5YbsCsg+wWZndS57vdlnPL2vLI8FfPJTecdUqOi5yovhpBYUuUwpHVpJk1IWKQK4uCmfNBF1ommGD2diIVtqpco+mCBVFkgj71B+5ez6k6R+NTOm4wZ3EyL6DeDmgMuRVVuuN86rSEq0AsKd1Suey3Wh2pqX+J4cuNatQ8hWIFg2DrUwZLaH2WM3NjB299J2D7zxzo1mIu2kkL/ad/MzCc3nX2yfBD/HrmR12HBaplYUsWEmazmarrghrdo8f9OdFTiJk00P4Jat2xTvUTRBwukyiHu8fnccqMndqknxEpVbuwPf9ovm6gbpRaxwonIzfgUYIzcuBdwZNHkjJ0PX9x50DLr233K5HKjF26PfAjyrCR/oSZHbvs2y4DF0Y6Eq6yeiGpZmLjwlph3Uz7o4mgn8mAYZ4nbwU70oKleQlo5HyyQKoegp/JIRU7L3HKzlHNOVW7sBd1+Tc56nkK5oSTrcRkjN2Cby5m039M3Xrex2BcCXWn8uHRCJpcbkDhpvL3ikMAevNa5z0vPulASpG2qLCBWxSj+7qF1rhSwcPed6ETYOv+8E1EEmqR6CRHWomqkikGFG5FdS7khpO0F3Y7wfs+VyY1rMvK7m5FyYx/TyM+y7Jqo81mvbUKy6V4sYr7JbzbmkBuJDahFgkPVgTt548GBqpJdSYEYU+UQffzhtAk65QZ0Ii4fgbwTXX/ZNZdFQ9rJnxhT5Sg6C1lbrbfcJGsF67mU3BxvEdEjN278/Q9HioyUG6ZpX+ez6jP4uo3gxEVSJDsqytxLqjnkRtMQqP2emECyQqCBTVw5NQE6vH79ujjkHYo9VQ6RMUDeW7/cLNuJGPHPzycSg529uXIdHLgiNyB9CustN+0I7/c8hnCAC7aedMB9zZy/87IUI+UG3GuHWnpet3HP7HUA2K2Kzb2kmkNuQKMLWIygERxC4LMsT1wYa/ICiMvW1harCbbYtas8VQHZlSoGxAJSxdAvN7BUJ0xHh8oUdnZ2FjPe3dzcFCN7XWoDsitVMji69rlmcuOeFDSyA/fUs60g9sZO6Xxs4bSvs5WL8DEJzni5cVPQIk+O29ijoy+2STHrmYmZ5AaIK1kCFCFc88BjGSJPN3Kw1xYp4pAqHSwlNzWKnUBj1kwhnzLI3lQpoVqwZnID9s75zrUHeWAgSfkbgNxj5e8Hk1DkEZ4/78RTeiYfkReX80WBUzSK/l1ktv2tcCsu3xqHFsbLDeQ/QTjX8bqNE5T8GY0dG0mQnJY5mE9uBDIUUhKCk1s08cYHllHtw9GEENrY2MCZLZ+xpH0lcINU6YAB4O8if5JOFJSRkYvuMGs+17QSeg7NGcg7kYa1nhmbONQGaVnKOacqN/kLrCiI/AcGH3792L74XyvFb6BrP/zRMvib7GIpLiXc2k0KnjIR+UGGnUhtPTKJ3OSDGUy40A67jCrKk1PVkcvGBnPLTfBroCo33PMHpYGCT0ML8nyhtqzQUhSpPC3KS3I9Su2hSa0U059J5CbPtorHsrgTW0u+nJDNtKQKuQnGU5Ub4PrOX2O1hQyImCGQ7MpLi3vQoHBXL/pLqf1pmBuP9hutGguTPE1rlOKAJ5EbsP0MPoFyz7nQlLSjhP1nmmlJFXITjKclNwLXPRKgmQ4xT9hwC7U3Zz4TP+rD1U+QNy56/OmBbulKOqQQUbUbuFBsxYEGv6tiJAyvoVaMvDFgBiZt2Q6ugBowcj3i4JiZqUxQ5pisFThv6kxpn8bjEXITjGdYbs4SxCFxjnxIIaRZ3M2RC+RwFNUaZCtZ14eQm2A8vy65OUHsmm4wWzmFhNwE4wm5WQUsA+13TKvJp6Yl5CYYT8jNKmDhplrz3oiHzSdIyE0wnpCb2bn37JVNbdovXp9aQm6C8YTczAhrqPPbe1Zr1jS1gZCbYDwhNxODoCAr7x79i/FS3q7/x3Wnn5CbYDwhNxPjJEYLWjPH6zArI+QmGE/IzcQ4lZHy0ebu+uY1QshNMJ6Qm4nR957ZfrL9w9UHz9fxa++cly9fPn36NP/zUUHQT8hNEAQrIuQmCIIVEXITBMFKePPmf4GyDTAL6GK4AAAAAElFTkSuQmCC
Inventory data.xlsx|||`Inventory data$`
False
1
Inventory data.xlsx
Query=`Inventory data$`
Invoice Data.xlsx|||`Invoice Data$`
False
1
Invoice Data.xlsx
Query=`Invoice Data$`
Products On Order Data.xlsx|||`Products On Order Data$`
False
1
Products On Order Data.xlsx
Query=`Products On Order Data$`
Warn
date = DateTimeParse([ETA: Month]+PadLeft(tostring([ETA: Day]),2,"0")+ToString([...
Error
Location ID - Ascending
Prod ID - Ascending
date - Ascending
First
1
First 1
ETA Date = IF IsNull([ETA Date]) THEN "No PO" ELSE [ETA Date] ENDIF
On Order Qty...
C:\Users\HSHI9\AppData\Local\Temp\Engine_15664_722b5b3355fc498582cf003880ba4de8_\Engine_13584_53105a59b7fa4c028a7ed16923f20ff3_.yxdb
Single
Profile
Final Output
Solution
C:\Users\HSHI9\AppData\Local\Temp\Engine_15664_722b5b3355fc498582cf003880ba4de8_\Engine_13584_1b74ec2535044d21ac4b46298dd752af_.yxdb
010
MCALLEN
18
Peter Cotton Ale 24pk Can
6249
5952
258.782608695652
0
No PO
010
MCALLEN
310
Baby Got Bock 1/4 Keg
2052
1888
82.0869565217391
980
July 4, 2019
010
MCALLEN
311
Bock to The Future 1/6 Keg
5334
11050
480.434782608696
7056
July 8, 2019
010
MCALLEN
318
IP Ale 12pk Can
47795
58899
2560.82608695652
3120
June 29, 2019
010
MCALLEN
322
Audrey Hopburn Belgian 6pk Bottle
8736
6636
288.521739130435
1344
July 8, 2019
010
MCALLEN
323
Bitter Women IPA 16oz Bottle
2460
1707
74.2173913043478
0
No PO
010
MCALLEN
328
Hoptimus Prime 25oz Bottle
2278
8233
357.95652173913
8640
July 1, 2019
010
MCALLEN
350
Smooth Hoperator 6pk Can
416
744
32.3478260869565
144
July 2, 2019
010
MCALLEN
353
Pathological Lager 4pk Can
3455
7103
308.826086956522
1440
July 3, 2019
010
MCALLEN
370
Robohop 1/2 Keg
1015
23820
1035.65217391304
16954
July 1, 2019
010
MCALLEN
372
Hoppy Ending Pale Ale 32oz Can
7588
12604
548
6160
July 1, 2019
010
MCALLEN
50
Tricerahops 40oz Bottle
75
122
5.54545454545455
0
No PO
010
MCALLEN
53
Gary Brewsey 25oz Can
2770
2541
110.478260869565
0
No PO
010
MCALLEN
70
Hold Me Just a Little Bit Lager 6pk Can
1171
3063
133.173913043478
0
No PO
010
MCALLEN
718
Hoppy Seconds 1/4 Keg
14196
24121
1048.73913043478
6240
July 1, 2019
010
MCALLEN
72
Mother in Lager 20pk Bottle
1775
1893
82.304347826087
1120
July 4, 2019
010
MCALLEN
722
Joose On The Loose IPA 1/2 Keg
10922
10560
459.130434782609
3248
July 1, 2019
010
MCALLEN
750
Honey Boo Brew 1/2 Keg
517
1171
50.9130434782609
360
July 2, 2019
010
MCALLEN
770
Stout It Out 6pk Bottles
18546
16921
735.695652173913
0
No PO
010
MCALLEN
772
The Pursuit of Hoppiness 18pk Can
15927
15377
668.565217391304
6160
July 4, 2019
020
HARLINGEN
18
Peter Cotton Ale 24pk Can
5338
4271
177.958333333333
0
No PO
020
HARLINGEN
310
Baby Got Bock 1/4 Keg
1091
1164
48.5
588
July 8, 2019
020
HARLINGEN
311
Bock to The Future 1/6 Keg
2860
5947
247.791666666667
4606
July 3, 2019
020
HARLINGEN
318
IP Ale 12pk Can
37388
38448
1537.92
9000
June 29, 2019
020
HARLINGEN
322
Audrey Hopburn Belgian 6pk Bottle
4870
3447
137.88
112
July 1, 2019
020
HARLINGEN
323
Bitter Women IPA 16oz Bottle
1408
669
27.875
0
No PO
020
HARLINGEN
328
Hoptimus Prime 25oz Bottle
2059
5459
218.36
2040
July 1, 2019
020
HARLINGEN
350
Smooth Hoperator 6pk Can
254
492
21.3913043478261
144
July 2, 2019
020
HARLINGEN
353
Pathological Lager 4pk Can
3608
4261
177.541666666667
1728
July 9, 2019
020
HARLINGEN
370
Robohop 1/2 Keg
2169
10168
391.076923076923
8722
July 2, 2019
020
HARLINGEN
372
Hoppy Ending Pale Ale 32oz Can
4236
6172
246.88
4480
July 1, 2019
020
HARLINGEN
50
Tricerahops 40oz Bottle
327
495
21.5217391304348
32
July 4, 2019
020
HARLINGEN
53
Gary Brewsey 25oz Can
606
1486
64.6086956521739
936
July 1, 2019
020
HARLINGEN
70
Hold Me Just a Little Bit Lager 6pk Can
940
1713
65.8846153846154
0
No PO
020
HARLINGEN
718
Hoppy Seconds 1/4 Keg
10397
13450
538
3000
June 29, 2019
020
HARLINGEN
72
Mother in Lager 20pk Bottle
971
1056
44
560
July 5, 2019
020
HARLINGEN
722
Joose On The Loose IPA 1/2 Keg
3263
3586
149.416666666667
0
No PO
020
HARLINGEN
750
Honey Boo Brew 1/2 Keg
284
557
24.2173913043478
152
July 4, 2019
020
HARLINGEN
770
Stout It Out 6pk Bottles
8980
8425
324.038461538462
0
No PO
020
HARLINGEN
772
The Pursuit of Hoppiness 18pk Can
6800
6347
253.88
3024
July 4, 2019
030
LAREDO
18
Peter Cotton Ale 24pk Can
2488
2468
107.304347826087
0
No PO
030
LAREDO
310
Baby Got Bock 1/4 Keg
295
618
26.8695652173913
588
July 5, 2019
030
LAREDO
311
Bock to The Future 1/6 Keg
3378
3947
171.608695652174
4998
July 8, 2019
030
LAREDO
318
IP Ale 12pk Can
30282
27863
1211.4347826087
1680
June 29, 2019
030
LAREDO
322
Audrey Hopburn Belgian 6pk Bottle
3111
2322
100.95652173913
0
No PO
030
LAREDO
323
Bitter Women IPA 16oz Bottle
1048
632
27.4782608695652
0
No PO
030
LAREDO
328
Hoptimus Prime 25oz Bottle
2059
7130
310
4080
July 3, 2019
030
LAREDO
350
Smooth Hoperator 6pk Can
166
298
17.5294117647059
88
July 3, 2019
030
LAREDO
353
Pathological Lager 4pk Can
3489
3742
162.695652173913
936
July 1, 2019
030
LAREDO
370
Robohop 1/2 Keg
4558
15452
671.826086956522
12936
July 1, 2019
030
LAREDO
372
Hoppy Ending Pale Ale 32oz Can
4242
7826
340.260869565217
2632
July 1, 2019
030
LAREDO
50
Tricerahops 40oz Bottle
21
40
2.66666666666667
36
July 3, 2019
030
LAREDO
53
Gary Brewsey 25oz Can
603
748
32.5217391304348
432
July 1, 2019
030
LAREDO
70
Hold Me Just a Little Bit Lager 6pk Can
412
1783
77.5217391304348
1764
July 4, 2019
030
LAREDO
718
Hoppy Seconds 1/4 Keg
9895
8013
348.391304347826
0
No PO
030
LAREDO
72
Mother in Lager 20pk Bottle
1586
1160
50.4347826086956
224
July 8, 2019
030
LAREDO
722
Joose On The Loose IPA 1/2 Keg
2598
2243
97.5217391304348
560
July 1, 2019
030
LAREDO
750
Honey Boo Brew 1/2 Keg
206
376
19.7894736842105
96
July 3, 2019
030
LAREDO
770
Stout It Out 6pk Bottles
5240
5662
246.173913043478
0
No PO
030
LAREDO
772
The Pursuit of Hoppiness 18pk Can
5501
4978
216.434782608696
1680
July 1, 2019
050
CORPUS CHRISTI
18
Peter Cotton Ale 24pk Can
2984
5443
236.652173913043
0
No PO
050
CORPUS CHRISTI
310
Baby Got Bock 1/4 Keg
1284
1405
58.5416666666667
0
No PO
050
CORPUS CHRISTI
311
Bock to The Future 1/6 Keg
3477
14252
619.652173913043
19992
July 3, 2019
050
CORPUS CHRISTI
318
IP Ale 12pk Can
22111
30445
1268.54166666667
0
No PO
050
CORPUS CHRISTI
322
Audrey Hopburn Belgian 6pk Bottle
5825
6006
240.24
1904
July 8, 2019
050
CORPUS CHRISTI
323
Bitter Women IPA 16oz Bottle
825
1729
75.1739130434783
0
No PO
050
CORPUS CHRISTI
328
Hoptimus Prime 25oz Bottle
236
5500
239.130434782609
3780
July 1, 2019
050
CORPUS CHRISTI
350
Smooth Hoperator 6pk Can
494
823
35.7826086956522
36
July 3, 2019
050
CORPUS CHRISTI
353
Pathological Lager 4pk Can
2497
2296
99.8260869565217
0
No PO
050
CORPUS CHRISTI
370
Robohop 1/2 Keg
306
14971
598.84
15288
July 1, 2019
050
CORPUS CHRISTI
372
Hoppy Ending Pale Ale 32oz Can
3887
8555
356.458333333333
4872
July 1, 2019
050
CORPUS CHRISTI
50
Tricerahops 40oz Bottle
17
109
4.73913043478261
76
July 2, 2019
050
CORPUS CHRISTI
53
Gary Brewsey 25oz Can
718
1103
47.9565217391304
0
No PO
050
CORPUS CHRISTI
70
Hold Me Just a Little Bit Lager 6pk Can
4510
3554
148.083333333333
0
No PO
050
CORPUS CHRISTI
718
Hoppy Seconds 1/4 Keg
11148
14999
599.96
3600
July 1, 2019
050
CORPUS CHRISTI
72
Mother in Lager 20pk Bottle
1678
2387
103.782608695652
1344
July 2, 2019
050
CORPUS CHRISTI
722
Joose On The Loose IPA 1/2 Keg
7208
6208
258.666666666667
56
July 8, 2019
050
CORPUS CHRISTI
750
Honey Boo Brew 1/2 Keg
358
777
32.375
80
July 2, 2019
050
CORPUS CHRISTI
770
Stout It Out 6pk Bottles
10061
12206
488.24
180
June 29, 2019
050
CORPUS CHRISTI
772
The Pursuit of Hoppiness 18pk Can
4844
6502
260.08
1904
July 5, 2019
060
EL PASO
18
Peter Cotton Ale 24pk Can
4914
5289
195.888888888889
5880
July 6, 2019
060
EL PASO
310
Baby Got Bock 1/4 Keg
1616
1634
60.5185185185185
0
No PO
060
EL PASO
311
Bock to The Future 1/6 Keg
10
0
0
No PO
060
EL PASO
318
IP Ale 12pk Can
19509
28990
1073.7037037037
30360
July 4, 2019
060
EL PASO
322
Audrey Hopburn Belgian 6pk Bottle
3760
5519
204.407407407407
2856
July 1, 2019
060
EL PASO
323
Bitter Women IPA 16oz Bottle
500
0
0
No PO
060
EL PASO
328
Hoptimus Prime 25oz Bottle
18367
26262
972.666666666667
5520
July 1, 2019
060
EL PASO
350
Smooth Hoperator 6pk Can
820
1595
66.4583333333333
284
July 1, 2019
060
EL PASO
353
Pathological Lager 4pk Can
20071
29606
1096.51851851852
5256
July 1, 2019
060
EL PASO
370
Robohop 1/2 Keg
8103
10091
373.740740740741
4704
July 1, 2019
060
EL PASO
372
Hoppy Ending Pale Ale 32oz Can
4589
13683
506.777777777778
4144
July 1, 2019
060
EL PASO
50
Tricerahops 40oz Bottle
267
327
13.625
0
No PO
060
EL PASO
53
Gary Brewsey 25oz Can
8382
11515
442.884615384615
0
No PO
060
EL PASO
70
Hold Me Just a Little Bit Lager 6pk Can
1794
2315
85.7407407407407
0
No PO
060
EL PASO
718
Hoppy Seconds 1/4 Keg
6653
10965
406.111111111111
3960
July 1, 2019
060
EL PASO
72
Mother in Lager 20pk Bottle
1918
2321
85.962962962963
1344
July 3, 2019
060
EL PASO
722
Joose On The Loose IPA 1/2 Keg
4726
6369
235.888888888889
5656
July 1, 2019
060
EL PASO
750
Honey Boo Brew 1/2 Keg
881
1512
60.48
304
July 1, 2019
060
EL PASO
770
Stout It Out 6pk Bottles
10139
11793
436.777777777778
540
July 1, 2019
060
EL PASO
772
The Pursuit of Hoppiness 18pk Can
7851
11183
414.185185185185
5768
July 4, 2019
Horizontal
challenge_198_Nini