2020: Week 11

This week at C&B Suds Co we're looking to improve and streamline our soap packaging and delivery process. We want to figure out how we can minimise wasted box space and minimise the number of separate boxes per order - a variant of the knapsack problem!

The Challenge

At C&B Suds Co we now use three different sizes of box for our soap orders:

• A bulk order box which can hold 120 soaps.
• A large box that can hold 24 soaps.
• A small box that can hold 6 soaps.

For all of our orders we want to minimise the number of boxes we use per order. At the same time, we also want to try and minimise unused space in boxes. 

For example, if an order came through for 32 bars of soap we could package this in 3 boxes:

• 1 large box of 24 soaps.
• 1 small box of 6 soaps.
• 1 small box of 2 soaps with the unused space for 4 soaps.

We want two outputs: 

• an overview for each order of the number of each box size used;
• and one that gives the details for each individual box being shipped out.

Inputs

There is 1 input file with 3 sheets:

• a table of orders:
• [Order Number]
• [Order Size]

• a table of box sizes:
• [Box Size]


• a 1000 row scaffold which you can opt to use:
• [Scaffold]

Input 1: Table of Orders

Input 3: Optional scaffold.

Input 2: Box sizes.

Requirements

• Input the data.
• For each order, figure out how many boxes of each size will be required.
• Arrange this information so there is a single row per order with different fields for each box size.
• Output the above.
• For each box, figure out how many bars of soap will be in that box.
• Assign each box in each order a unique ID, starting from 1 each in each order.
• The box ID should be ascending from the box with the most soap to the box with the least.
• Output the above as well.

We've found this challenge to be easier in 2020.1.3 and above but can still be completed in earlier versions.

Outputs

Output 1: Boxes Per Order

• 5 fields:



Output 1: Boxes per Order

• [Order Number]
• [Order Size]
• [Boxes of 120]
• [Boxes of 24]
• [Boxes of 6]
• The "Boxes of X" fields should contain a number to indicate how many boxes of that size are required for that order.
• 7 rows of data - one per order. (8 rows including headers).

Output 2: Soaps per Box

Output 2: Soaps per Box

• 5 fields*:
• [Order Number]
• [Order Size]
• [Box Number]
• [Box Size]
• [Soaps in Box]
• 33 rows of data - one per box per order (34 including headers).

*As of 06/03/2020, if you use a FIXED LOD field you cannot remove this field without generating an error. This means you may have an additional output field for this challenge.

You can check out and download the full outputs below for comparison:

• Output 1: Boxes per Order
• Output 2: Soaps per Box

Don’t forget to fill the participation tracker and share your solutions using #PreppinData on Twitter.