Challenge #66: My Wine Problem

Last week's challenge is posted HERE!

OK, I have a problem... for you to solve!

We are selling bottles of wine - each of which has a unique ID since it's a limited vintage.

Customers can request their wine bottles be delivered in various different sizes of delivery box.

Customers order wine in ranges - from ID 1 to 25 for example.

Challenge: Build a macro that takes in order ranges, allows the user to input the box size, and outputs the order broken into the requested box sizes.

There are 3 sample outputs displayed to test the different box sizes:
- Box size of 6
- Box size of 12
- Box size of 96

The macro must check for, or prevent 3 specific error conditions:
- a null value for either the from or to value on an order range
- non-numeric values in the from & to values
- situations where the from ID is greater than the to ID

BONUS - Instead of using an interface tool to allow the user to set 6/12/96 box sizes, build a batch macro that will package all three scenarios at once!
DOUBLE BONUS - Complete this challenge whilst listening to some UB40

challenge_66_start_file.yxmd

challenge_66_solution_1.yxzp

challenge_66_solution_2.yxzp

Macros

Preparation

Advanced

Intermediate

Transform

Interface

Accepted answers

All comments

alex

The solution output for #3 has box size of 96 not 120? triple bonus to do both :>

JoeM

Thanks for pointing that out @alex! I updated the exercise and the post to reflect 96. However, the beauty of this macro could be that you could test all packaging conditions Triple bonus?! I'm feeling generous - why not?

Philip

My solution. I use the same macro to output all three scenarios.

Spoiler

WineBottleGroupings.yxmc

Natasha

Definitely no bonuses for me as it took me ages and I appended all 4 box sizes instead of using a batch macro.

Spoiler

challenge_66_macro_NK.yxwz

MarqueeCrew

@JoeM,

I created a standard macro that calculates all 3 options at once. No batch required.

Cheers,

Mark

challenge_66_MarqueeCrew.yxzp

BenMoss

I'm not shooting for bonus points this time round

Spoiler

I believe I have catered for 2 of the three error conditions but I am a bit confused as to what is expected in the case of 'non-numeric values in the from & to values', but you can take a look at my macro too see how I have dealt with this.

challenge_66_BM.yxzp

NicoleJohnson

My solution! And my kind of problem!! (Admission: I finished this one with a glass of red, red wine right next to me. Cheers!)

Spoiler

Tested for 6/12/96/120 bottles of wine, just for kicks. Although let's be honest, 120 bottles is probably the only one that's necessary...

Figuring out the macro portions was the hardest part for me since i haven't had much exposure so far. Originally I tried to have a dynamic selection of box size as well, but I need to do a little more macro-playing before I go that route it appears, so we're sticking with basic inputs for this one. Included the app file I used to test my theories.

I might revisit this one though. I'm not convinced my conditions are working 100%. And I am convinced the box selection could be dynamic.

App for testing the macro with new values.

challenge_66_NicoleJohnson.yxmc

MacroTest.yxwz

MinaGO

Nice challenge! Wasnt as bad as I originally thought it would be but I definitely use too many tools. Personally I liked the idea of three separate outputs so I added a standard macro outside my batch to give S, M, L outputs. Cant upload packaged workflows for whatever reason so only screen shots this time .

Spoiler

JoeM

Wow @NicoleJohnson! Now there has to be some sort of bonus for that!

SeanAdams

:-) included 3 variants (like 3 different kinds of cheese to go with the wine)

- the standard macro

- the iterative macro

- the batch macro

sadly, my neighbours have a strict "no singing old UB 40 songs after 11:30 at night" rule so I couldn't add any music to this experience...

challenge_66_start_file.yxmd

toddhowl

I'm having some trouble getting my head around this. Looking at the input, we have at least 801 distinct bottles of wine. But it appears that we've sold bottles 1 through 5 to 3 different customers (orders 1,2, & 3) and bottles 6 through 12 to two different customers (orders 2 & 3). Shouldn't the macro check for that? We can't send the same bottle out in multiple orders. What am I missing?

SeanAdams

great idea @toddhowl - that does add an extra challenge to this, I like your suggestion.

So are you thinking of building a macro that has an extra output with the errors/duplicate sales (I'm thinking of a batch macro that spits out your packing manifest on one output, a summary on the second, and possibly this kind of error on the third)?

Give it a bash - would be very interested to see your approach!

NaiLou

I didn't have a chance to create a batch macro, unfortunately I only had white wine stocked in the fridge

Spoiler

Challenge 66_NL.yxmc

alex

I completed mine as a Batch Macro and did not use a multi-row tool. Tested for 3 scenarios and set to stop passing records if they are encountered.

Edit: The workflow has a sixth order to test errors. Delete row 6 from Text input to see a completed workflow.

Spoiler

challenge_66_AC_1.yxzp

alex

@toddhowl @SeanAdams- based on my understanding of the challenge it is limited quantities, not distinct ids - more like UPC than VIN. $.02

Joe_Mako

I attempted a Batch Macro with the Error checking, and aimed for the least amount of tools in the Macro. I got 7 including interface tools.

You can rewire the input to use the Text Input tool that has the error causing situations (or Union both inputs to see how it handles a mix of good and bad records).

Spoiler

Challenge 66.png

in the Macro:
1- Macro Input, set to not show field map, to allow for non-integer fields be used as input (so we can capture the error)

2- Message, set to Before Rows Where Expression is True:

IsNull([StartingBottleID])
OR

IsNull([EndingBottleID])
OR

NOT IsInteger([StartingBottleID])
OR

NOT IsInteger([EndingBottleID])
OR

ToNumber([EndingBottleID],1)<ToNumber([StartingBottleID])

I decided to just Error and let it continue to work, so all errors can be seen, and acceptable records will continue to be processed.

3- Generate Rows, will create a RecordID for each Bottle, starts counting at 0, and ends at the difference between the start and end. I made use of the optional arguments in the ToNumber() function.

4- Control Patameter, to bring in BoxSize

5- Formula, to calculate:

BoxSize:
Max(ToNumber([#1]),1)
If an invalid value is entered, BoxSize will become 1

BoxNumberForOrder:
FLOOR(([RecordID])/[BoxSize])+1
Bin RecordID based on BoxSize

BottleID:
[RecordID]+ToNumber([StartingBottleID])
Convert RecordID into BottleID

6- Summarize, aggregate data to desired level of detail.

7- Macro Output, no config needed

I did not change any of the defaults in the Interface Designer.

Challenge 66 - Joe Mako.yxmd

Challenge 66 Batch Macro.yxmc

SeanAdams

Thank you @alex and @Joe_Mako for spotting that I posted the wrong file.

Correct file attached now (part 1 of 2)

BatchBuilder.yxmc

BatchMacro.yxmc

challenge_66_SeanSolution.yxmd

Compare2Sets.yxmc

estherb47

I might have had a little too much fun with this one....

First assumption that I made was that the first macro would divide up one order at a time, and allow the user to put in the first and last bottle they want through an interface.

Went with a tiled solution instead of the multi-row formula method.

And, naturally, added some graphics for custom buttons.

Spoiler

Spoiler - one at a time with user input

Spoiler

Batch macro solution. Outputs all 4 box sizes at once (6,12,96,120)

WineMacroBatch.yxzp

WineMacroWithUserInput.yxzp

SeanAdams

I love the little wine themed icons Esther :-) Did you come up with a good icon for your wine-box packing company (thinking of a stick figure, some boxes and wine and lots of question marks)

😁

LordNeilLord

I avoided this one for a while but it was actually easier than I thought it would be!

Spoiler

challenge_66_LNL.yxzp

vishalgupta

challenge_66_Vishal.yxzp

patrick_digan

Spoiler

challenge_66_start_file.yxzp

patrick_digan

Inspired by @Joe_Mako, I was able to get the macro down to 6 tools

Spoiler

I just packed a bunch of info into the generate rows tool and then deconstructed it with a formula tool

It's obviously not very easy on the eyes or straightforward...

challenge_66_start_file2.yxzp

samN

Fun challenge. Used a batch macro and will await my bonus points

Spoiler

I just filtered by the conditions in my macro and if they didn't fulfill them it did not try to complete those orders. No errors are ever thrown if this happens.
I also left some of the filtering outside of the macro because i felt like it would be easier to change in case we get additional box sizes. I like my macros to be more than just one time uses and work around them instead of constantly changing them. Granted not sure ill ever use this one again, but you get my point, reader.

challenge_66.yxzp

ggruccio

Did the challenge and the bonus.

challenge_66_bonus.yxzp

challenge_66_part1.yxzp

LandonG

Solution attached. Great success!

challenge_66_start_file.yxzp

philipmannering

I want the bonus points - give them to me now. Solution attached.

Spoiler

This is my workflow

This is my macro

challenge_66 - My Wine Problem.yxzp

jasperlch

Solution attached.

challenge_66_JL.yxzp

jamielaird

I went for a fairly simple approach. First time I've used the Test tool and I can see this coming in handy in future.

Spoiler

challenge_66_solution_JL.yxzp

katelynhweber

My solution, which uses the batch macro approach mentioned in the bonus.

challenge_66_soln_kw.yxmc

JosephSerpis

Challenge Completed

Challenge_66_Joe_Serpis.yxzp

daniel_ferreira

Follow my solution package attached...

I solved it using a batch macro and some message tools to avoid data input erros, but for me the best thing was to listen to UB40 during this exercise. It was really amazing!!!

Spoiler

Workflow

Batch Macro

challenge_66_solved.yxzp

dsmdavid

Built as an app with a batch macro inside...

Spoiler

Catching errors:

Catching errors

Main macro

Workflow of the app (+ checking results)

solved#66_macro.yxzp

tcroberts

Here's my solution:

Spoiler

The stuff outside the macro isn't particularly exciting, but this should allow the user to specify the box size and will output correct results for each.

Heavy use of the Multi-Row formula tool to allocate boxes and correctly identify the BottleIDs in each box. RunningTotal was invaluable in preventing box overflows as well.

Did anyone find a way to do this without just appending box size to every observation? My only thought would've been to set a global constant to BoxSize, but I was unsure how to go about that with a user-specified input value.

Really enjoyed this challenge, it was a tough problem with some quirks to make it feel a little more like a real-world problem.

challenge_66_wineproblem_completed.yxzp

danilang

No bonus for me either. But on the other hand...Yay 1st Macro built from scratch! Cute little icon and everything

Spoiler

Dan

challenge_66_solution_DL.yxmd

Spoiler

Cheers!

challenge_66_Joben_Solution.yxzp

ConradW

66 Snap.png

Spoiler

Weekly Challenge.yxmc

Weekly Challenge.yxwz

bkclaw113

As a few people mentioned I found how this was worded to be very confusing as well. They kind of missed the point of having numbered bottles . . . they need to be unique.

However, for this challenge to work you need to assume that the order number and the bottle number make up the composite unique key, so OrderID1 can have BottleID 1, and the completely seperate OrderID 2 can also have a bottle with BottleID1. Think of it more like 1.1 and 2.1.

Anyway I did end up with a batch macro handling the box size, calling an iterative macro sorting the bottles into the boxes.

challenge_66.yxzp

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335