General Discussions
Discuss any topics that are not product-specific here.
Today's AoC is fun.
At first, I tried using a "Generate Rows" tool, but realized it would run forever, so I rebuilt my workflow.
For Part 1, I created combinations of IDs and ranges by appending tool, and then checked if the ID was within the range.
For Part 2, I sorted the ranges by their start value and then merged any that overlapped.
My solution.
I couldn't come up with a good solution, and it took longer than I thought it would. Everyone else has created simple solutions, so I'll take another look at them.
It was a process of trial and error using Spatial tools and Batch Macros. I needed several breakthroughs to arrive at the correct answer.
Getting difficult...
If we can have a few rows that keeping overlapping then they will be in one greater range and the min and max will be the LB and UB.
max(start1, start2) <= min(end1, end2)
Sneaky example which pushed me down the Generate Rows route initially. Lesson learned to look at the data first!
Another 2 stars! Certainly getting tougher than day one, but managing to keep the pace up so far.
Part 1
1) Today's input data actually had two separate lists - the list of fresh ranges and the list of ingredients. To get started, I separated the two lists by filtering to records containing a "-", as these were the ranges. With the fresh ranges identified, I could then use the "-" delimiter to parse the upper and lower bounds into separate rows, before renaming and changing the new columns' data types to numeric.
2) Next I appended all the ingredients onto all the ranges. Yes this grew the data but we only had a few hundred rows of data each side, so light work for Alteryx. With the appended data I could then filter to the cases where the ingredient ID, fell within the upper and lower bounds.
3) The steps so far, will likely have resulted in some ingredients being approved twice if they appeared in more than one cell range. To get around this I did a count distinct on the ingredients, to get my final answer.
Part 2
For part two, we aren't interested in the ingredients list at all. All we need to examine is the fresh ranges. With this in mind, the first step for part two can be the same as step on from above.
1) Once we have the list of ranges, the next step is to simplify them. To tackle this, I started by sorting the Lower and Upper Bounds in ascending order. This then allowed me to keep track of the "Rolling Maximum ID". What I mean by this, is that at any point, I would know what the previous, highest ID value had been. i.e:
| Row ID | Lower Bound | Upper Bound | Rolling Maximum ID |
| 1 | 0 | 10 | - |
| 2 | 6 | 15 | 10 |
| 3 | 16 | 30 | 15 |
| 4 | 21 | 25 | 30 |
As you will notice, we can now identify which lines have already been accounted for. The upper bound in Row 4, is lower than the Rolling maximum ID meaning that all the records represented by the range 21-25, have previously been seen. Therefore, we can remove this row from the process.
2) You will have likely noticed, that we still need to deal with the overlap, between record 1 & 2. The way I approached this, was to update the Lower Bound, so that the two groups, no longer overlapped.
| Row ID | Lower Bound | Upper Bound | Rolling Maximum ID | Updated Lower Bound |
| 1 | 0 | 10 | - | 0 |
| 2 | 6 | 15 | 10 | 11 (the Rolling maximum ID, 10, is greater than the Lower bound, 6. We know all numbers up to and including the Rolling Maximum ID have been delt with, so we only need to carry on counting IDs from that point forward). |
| 3 | 16 | 30 | 15 | 16 (as the lower bound was greater than the Rolling maximum ID, we knew all numbers in this range were "new") |
The formula to account for the above is actually quite simple. To update the lower bound we just need to take the maximum between the Lower Bound and the (Rolling Maximum ID + 1). Once the lower bound is updated, its easy enough to count the number of IDs within each range. The count is simply the Upper Bound - Updated Lower Bound + 1 (the plus one is to account for the fact that the ranges are inclusive).
| Row ID | Updated Lower Bound | Upper Bound | Rolling Maximum ID | Count |
| 1 | 0 | 10 | 0 | 11 |
| 2 | 11 | 15 | 10 | 5 |
| 3 | 16 | 30 | 15 | 15 |
3) The final step is to simply sum all of the counts to find out the total number of fresh IDs!
Looks lik I did the same/similar to others for part 1.
Part 2 just isn't working for me. I'll have to come back to it later.
Was tempted to be lazy and brute force the answer on Pt 2, but since the explanation "I burnt my work computer to a crisp looking for fresh ingredients for elves at the north pole" probably wouldn't fly at my company - I decided a more clever solution was needed.
Pt 1 check if in range:
Pt 2 Update Start
Pt 2 Update End
