Challenge #66: My Wine Problem

[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...

[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

   

[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

[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

 

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.

[SeanAdams]

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

Correct file attached now (part 1 of 2)

[SeanAdams]

correct files part 2 of 2

[JoeM]

This week we will give you some different ways to execute the problem. Solutions posted!