#SANTALYTICS Part 3

[TaraM]

#SANTALYTICS lives on! As @SophiaF said "How did  Santa ever do this without Alteryx?" We all agree and give thanks for Alteryx everyday.

In Part 2 we identified the hubs Santa will visit this season and the minimum weight that can deliver presents to every kid in those hubs with respect to their present score.

But what about maximizing the space of the sled so that it’s full,
while accounting for how much weight the reindeer can pull?

Can you help the elves revisit the present assignments for each nice kid now that we know how many reindeer Santa is attaching to the sleigh this year? They want to make sure every kid is getting the biggest and best (priciest then heaviest in priority order) present they earned in their present classes. The kids who behaved the best should be the first to get their presents adjusted - they earned it!

Goal of Part 3:
Determine the exact present distribution of the nice kids without exceeding 422 lbs per hub - prioritize price, then weight and assign to the nicest kids first

• Part 1: November 30, 2016: #SANTALYTICS Part 1: Solution and Behind the Data
• Part 2: December 7, 2016: #SANTALYTICS Part 2: Solution and Behind the Data
• Part 3: December 14, 2016: #SANTALYTICS part3: Solution and Behind the Data
• Part 4: December 21, 2016:  #SANTALYTICS Part 4: Solution and Series Finale

[ggruccio]

This was a bit of a challenge....I'm guessing others experienced the same since this is the first response.

I started with a cartesian join of sorts where all kids in each present class were assigned all possible gifts and then ran it through an interative macro progressively removing the better gifts, re-ranking etc, for the kids in each hub from best to worst.  I had a built in criteria that would allow each kid to receive their "worst" gift before going onto the next child.  When the weight came in under 422 pounds for the hub, the macro would write the output.  Over 422 pounds and it would go back through the workstream.

While I think I was onto something - I got it to a point where I was caught in an endless loop with only a few records left!  I must have had a hub or two where my methodology never got below 422 lbs.

Hoping others can provide Santa more help!

I've attached my workflow, macro, and data in a zipped file.

[JohnJPS]

I've also failed Santa... I've attached an attempt to simply process one hub using the optimizer tool with dynamically generated constraints (e.g. for the hub in question: required number of presents in each present category and the max allowed weight)... once working this would still need to be wrapped in an iterative macro. If a solution along these lines is revealed, I look forward to it! Brain = fried!  (Kaggle shares blame for that, though.)  

[Philip]

A little late, but here is my solution. After thinking about ways to approach this problem, I decided to take an interative waterfall approach. It's convoluted, and not optimized by price, but it's the only way I could think of.

Spoiler

1. Join all recipients with possible present choices based on present score.
2. Iteration 1 samples from available choices until the weight is less than the 422 limit by assigning a random ID to each record, ordering the records by the random ID, then taking the first record for each recipient.
3. Filter to include presents with weight less than the median of the reamaining hub group's presents by present score to reduce weight possibilities for randomized sampling.
4. Repeat steps 2 and 3 until every hub has been assigned presents.

Hub #1 was a problem, so I just took the minimum weight present for each present score. Even then, it only slipped barely under 422 lbs at 421.9468.

I would have liked to learn to use the Optimize tool for this, but I guess that will have to wait for the New Year's Resolution.

[SeanAdams]

Solution attached - we packed every inch and corner of Santa's vehicle - in fact, if he breathes too deeply something may fall out...

Spoiler

- Packed each hub with the lightest weight possible in the present group
       - Check if any hubs are overweight - luckily none.
       - add a column for the total weight for the hub, and any spare capacity

Then throw this through an iterative macro.
- In each iteration - take the nicest person from each hub and look for a higher rank present for this person
        - Eliminate presents with a lower rank
        - Eliminate presents that break the weight limit
- Once we've found the best present for the nicest person - remove this person from the set
         - update the latest total weight for the hub and the latest spare capacity
         - Send the remaining people through the iteration

This gets you to a good answer.