Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
General Discussions
Discuss any topics that are not product-specific here.
Passing the turning point Day13, 2nd half of AoC has started from today.
Spoiler
I put one cycle in single macro, it seems messy though
If you are stuck in part2, collapse spoiler 👇
Spoiler
Part2 requires too many iteration, but actually you don't have to. Let's try 1,000 loop first, and find the pattern of result.
Today was very interesting, and it was very satisfying getting to reuse a macro I built for Day 13. I definitely like how simple all the standard macros make the workflows look. I may add explanation of algorithm tomorrow. This problem is much simpler, but the solution is quite elegant.
Edit: Added my workflow
Algorithm:
Spoiler
This problem was a super fun to solve, and thinking of a clever way to leverage the tools at your disposal is key to building an efficient workflow for this problem.
Part 1 requires us to perform a North Slide, and then perform a Load calculation. Since the data in Alteryx is effectively a list of strings, one of the first things to recognize is how many string operations we have at our disposal:
Length(), ReplaceChar(), PadLeft(), PadRight(), Regex_CountMatches(), etc!
It will thus be much simpler trying to work with strings than trying to manipulate data across the rows of our data. One of things that stood out to me was if I had a string of unimpeded "round" rocks ('O's) with empty spaces around them, it would be simple to slide them all to the left ("West" Slide):
This is a great start, but doesn't yet do what we need - sliding rocks North. But if we bring back a concept from Day 13, then this process will be over in no time: Transposition! If we Transpose our matrix (swap rows and columns), then perform our West Slide and Transpose back, we will have effectively performed a North Slide! Now all that's left is to calculate the Load. I didn't actually think about doing this, but we can make use of the Regex_CountMatches() function to count the O's in each row, then multiply by the distance from the bottom of the matrix (101 - [RecordID]) in this case, and Summarize via Sum to get our answer!
Part 2 is scary to look at and think about because 1,000,000,000 is a big number, and big numbers are scary, but before we resign ourselves to waiting 10 years to get our answer, we should spend a little time thinking about a better way. Building a workflow to handle the spin cycle is an exercise left up to the reader (as it only entails finding clever new ways to convert the input into a form that we can West Slide and then converting back for all the cardinal directions). Instead, we will tackle process here.
Lets think about one of the simplest input structures we could have: all O's or empty spaces '.'s. In this case, it is quite easy to recognize that every cycle will return you to the point you started, and will thus have the same load. In other words, this simple case is periodic, with a period of 1! There is no guarantee that the added complexity of fixed points, #'s, in our data would also lead to a periodic outcome, but it couldn't hurt to try right?
So run a certain number of cycles and study the patterns. Does the Load converge on a small range of values? How often do those values appear? Try for 1000 cycles, or even 100-200. whatever seems reasonable to you. If you find a period, T, then all you need to do is extrapolate to find out which cycle index has the same modulus as 1,000,000,000
[Cycle Index] mod T = 1,000,000,000 mod T
The load at that [Cycle Index] is your answer (just make sure you are looking far enough into the cycling that a stable state has been reached, the first few records will not be consistent as the periodic structure takes several cycles to get established!)
Part 1 requires us to perform a North Slide, and then perform a Load calculation. Since the data in Alteryx is effectively a list of strings, one of the first things to recognize is how many string operations we have at our disposal:
Length(), ReplaceChar(), PadLeft(), PadRight(), Regex_CountMatches(), etc!
It will thus be much simpler trying to work with strings than trying to manipulate data across the rows of our data. One of things that stood out to me was if I had a string of unimpeded "round" rocks ('O's) with empty spaces around them, it would be simple to slide them all to the left ("West" Slide):
| Step | Data | Original Length | Next Instruction |
| 0 | ..O..OO..O | 10 | ReplaceChar([Data],'O','') |
| 1 | ...... | 10 | PadLeft([Data], [Length], 'O') |
| 2 | OOOO...... | 10 |
So the next question is how can we handle cube shaped rocks ("#")? Since #'s don't slide, then the space between 2 #'s would qualify as a string of unimpeded "round" rocks with empty spaces around them (see above). This means if we split our string with Text to Columns over all #'s (however, split to rows - not columns), then we now have a large list of strings that we can perform our "West" Slide on. After Summarizing via Concatenation, to replace the #'s that we removed, we will have successfully slid all rocks West.
This is a great start, but doesn't yet do what we need - sliding rocks North. But if we bring back a concept from Day 13, then this process will be over in no time: Transposition! If we Transpose our matrix (swap rows and columns), then perform our West Slide and Transpose back, we will have effectively performed a North Slide! Now all that's left is to calculate the Load. I didn't actually think about doing this, but we can make use of the Regex_CountMatches() function to count the O's in each row, then multiply by the distance from the bottom of the matrix (101 - [RecordID]) in this case, and Summarize via Sum to get our answer!
Part 2 is scary to look at and think about because 1,000,000,000 is a big number, and big numbers are scary, but before we resign ourselves to waiting 10 years to get our answer, we should spend a little time thinking about a better way. Building a workflow to handle the spin cycle is an exercise left up to the reader (as it only entails finding clever new ways to convert the input into a form that we can West Slide and then converting back for all the cardinal directions). Instead, we will tackle process here.
Lets think about one of the simplest input structures we could have: all O's or empty spaces '.'s. In this case, it is quite easy to recognize that every cycle will return you to the point you started, and will thus have the same load. In other words, this simple case is periodic, with a period of 1! There is no guarantee that the added complexity of fixed points, #'s, in our data would also lead to a periodic outcome, but it couldn't hurt to try right?
So run a certain number of cycles and study the patterns. Does the Load converge on a small range of values? How often do those values appear? Try for 1000 cycles, or even 100-200. whatever seems reasonable to you. If you find a period, T, then all you need to do is extrapolate to find out which cycle index has the same modulus as 1,000,000,000
[Cycle Index] mod T = 1,000,000,000 mod T
The load at that [Cycle Index] is your answer (just make sure you are looking far enough into the cycling that a stable state has been reached, the first few records will not be consistent as the periodic structure takes several cycles to get established!)
Workflows:
part 2 take 20~mins with AMP engine on.
Spoiler
part2: find the pattern.
I create a yxdb file to store history to map later, once found jump to nearest count.
workflow
part 1 macro
part 2 macro Note: ensure the engine compatibility mode is on.
I create a yxdb file to store history to map later, once found jump to nearest count.
workflowpart 1 macropart 2 macro Note: ensure the engine compatibility mode is on.
Today's one is interesting.
Spoiler
My WF run in 5 sec but some trial run is required to find the loop.
My workflow is very messy.
Spoiler
Below is a snapshot of how I executed the north tilt.
I repeated the same logic with some tweaks to create a full cycle which is my iterative macro.
There was some manual work involved in the end to spot the pattern to be able to get the load for 1000000000th cycle.
I repeated the same logic with some tweaks to create a full cycle which is my iterative macro.
There was some manual work involved in the end to spot the pattern to be able to get the load for 1000000000th cycle.
My solution.
This puzzle reminds me Day17 in 2022(TETRIS!!).
Spoiler
I struggled to find the exact repeat point. This task is very difficult for Designer, but my eye can find it easily. So I found that point on Excel and then I remade the workflow using that position.
And I used the Generate Row tool for "Loop".
Iterative macro:
And I used the Generate Row tool for "Loop".
Iterative macro:
I really enjoyed today's challenge - very interesting! I'm adding this to my list of problems to keep working at - I don't love my P2 approach and know that I could implement some logic for earlier stopping, just need a fresh brain to tackle it!
Spoiler
Part 1: My approach here was to find the nearest # upstream from every O. Then I took the difference between the RowID of the O and the RowID of the # to find each O's new placement and calculate the load.
Part 2: I wrapped my logic from P1 into a standard macro for easy repetition. I then created a macro to cycle through that logic 4 times. To shift the direction, I just recalculated my RowID and ColIDs and reshaped my data to "rotate". I then wrapped that "cycling" macro in an iterative macro and ran it 500 times. I realized that at some point the tilting was likely to result in a cycle of repeating patterns and my job was just to find that pattern, and which value in that pattern would be present at iteration 1billion. Instead of stopping my macro when that was found, I just picked 500 iterations and hoped that the cycle would emerge by then. Luckily, it did, and I could isolate the cycle with the load value that I needed.
Workflow: There is still some noise in here from another approach I tried, which was to run the iterations until the Load stopped increasing. Turns out that wasn't the proper logic even though it worked for the sample data :)
Standard Macro for Tilting the Table:
Standard Marco to Cycle the Data:
Iterative Macro:
Part 2: I wrapped my logic from P1 into a standard macro for easy repetition. I then created a macro to cycle through that logic 4 times. To shift the direction, I just recalculated my RowID and ColIDs and reshaped my data to "rotate". I then wrapped that "cycling" macro in an iterative macro and ran it 500 times. I realized that at some point the tilting was likely to result in a cycle of repeating patterns and my job was just to find that pattern, and which value in that pattern would be present at iteration 1billion. Instead of stopping my macro when that was found, I just picked 500 iterations and hoped that the cycle would emerge by then. Luckily, it did, and I could isolate the cycle with the load value that I needed.
Workflow: There is still some noise in here from another approach I tried, which was to run the iterations until the Load stopped increasing. Turns out that wasn't the proper logic even though it worked for the sample data :)
Standard Macro for Tilting the Table:
Standard Marco to Cycle the Data:
Iterative Macro:
@CoG
I was really wondering how di you put it off with 2 Fofrmula tools. Thank you so much for explaining it in detail.
My approach is more like bubble floating up.
I also seperate the Rows based on # after converting # as 9, O as 6, and . as 3.
then we can use one Sort tool to do the magic. Number is easier to handle than the Letter 😂
And you and @gawa mentioned about the patterns and I plotted it for only 100 cycles with test data and it is beautiful.
