Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Leading up to December of 2024 (the tenth year of Advent of Code), as a new Alteryx ACE, I was suckered into finally “trying” Advent of Code. I had heard whispers of this event over the years, but had never jumped in myself previously. I figured I’ll give it a shot, at least try a few problems and see what happens.
I was given fair warning that in the AYXxAoC circle, it is more important to go for completion than for finishing a problem first. This is especially relevant for me, considering I live on the East Coast of the US and these problems are released at midnight, a distinct disadvantage compared to say my Alteryx friends in Japan that get an early afternoon start each day (AoC 2025 business trip to Japan for the month of December anyone?). The first problem this past year happened to release over the weekend, yet I decided to wait until the next day to solve it at a leisurely pace… only to wake up and find out it could be done in a couple minutes tops 🙁 (word to the wise… the difficulty ramps up and it doesn’t stay like this for too long). Needless to say, the next night I was ready and back at it when the clock struck midnight and problem 2 was released.
Fast forward through many late nights and early workdays later (a few times I fell asleep around 3 am and then was off to work at 8 am… Celsius and I became good friends last December) to Christmas Day, and I ended up as one of only 8 (at the time) to get to 50 Stars (complete both parts of all 25 days’ worth of challenges) solving everything in BaseA. If that wasn’t enough, for some crazy reason that I still haven’t worked out, in early January I decided to take on the ultimate Advent of Code challenge: a YEAR of AoC to start back at the very beginning and complete all previous years (10 years – 500 stars total) before AoC 2025 starts in December! As of this writing, I am currently halfway there (2015 ✓ 2016 ✓ 2017 ✓ 2018 ✓ 2024 ✓ → 250/500 Stars), pacing at one year completed per month… Will I make it to the end in time? Only time will tell.
In the meantime, for all the returning and brand-new Alteryx enthusiasts that wish to come join our rag tag group on this AoC adventure, I decided to compile a short list of Tips and Tricks based on my experiences so far through half of all Advent of Code problems (the real reason you are here). By no means are these complete (PLEASE comment below with extra tips you wish to share), but I figured this was a good starting point. Besides, I can’t spend the time to write down all my secrets when I need to get back to solving actual problems if I hope to finish before year 11 kicks off…
1. Speed Up Your Starts
Various packaged “starter kits” are premade and available. Once a problem is released at midnight ET each day (or if you want to go back and tackle past events/problems like me), this is made infinitely easier to get started thanks to the work that others have already done. ACE Claire McCollough (@clmc9601) built an especially useful “starter kit” that I happen to use here. These problems are enough of a challenge as is—no need to add to the headache!
2. Save, and Save Often!
Many AoC problems are going to test the limits of Alteryx. As you test and re-test solutions memory constraints become a real issue, so don’t be surprised when you see those dreaded red X’s start popping up across your Designer screen (IYKYK). Autosave functionality will save you to a degree, but it’s better to be safe and be diligent with your saving than to be sorry.
Saving your solutions is also important for another reason. Advent of Code often does two things that you will be thanking yourself later for if you keep those workflow saves handy. For one, types of problems are often repeated, so macros you built in the past will likely come in handy in the future and may only need some slight tweaks rather than needing to rebuild from scratch. Secondly, at times, AoC problems are a direct continuation of previous problems. In these cases, you will really be kicking yourself if you don’t have that old workflow/macro to fall back on and you need to build from a blank canvas. This is even more evident if you are trying to compete for leaderboard placement.
3. Prep Your Input Data for Proper Analysis (Macro, Anyone?)
Text string “block” inputs often need to be split to rows and columns. This can be done in a few quick steps:
- Record ID to turn rows to Y-coordinates
- RegEx tokenize on individual characters (“.”) and split to rows
- Record ID grouped on Y-coordinates to create X-coordinates (Note: For older versions of Alteryx, use Multi-Row for this)
While this is a relatively simple sequence of tasks to execute, if this is being done over and over again for MANY different problems, why not build yourself a simple macro that you can reuse as needed? While it won’t matter in the long run for the sake of completion (other than just giving you back time, which is valuable in and of itself), if you are shooting for a spot on the leaderboard, those extra seconds/minutes add up.
4. Mazes
Many different AoC challenges across the years involve some version of a maze. In other words, programmatically building/following various branching paths to optimize your route to get from point A to point B. Sometimes this is as straightforward as going from one point to another given a set of “walls” and sometimes it may even involve reindeer races or Santa’s elves determining where to move across a map to then do turn-based battle with Goblins (some of these challenges can get pretty wild)… in all cases route optimization can be programmatically determined.
One way to do this is by implementing a Breadth-First Search (BFS) approach. BFS is an algorithm that involves searching a tree data structure for a node that satisfies a given property. In the case of a maze, we go from a start location/node and traverse every single branching path node (one expanding node at a time) until the end goal location/node is reached. In a maze, from any given location, you can move up to 4 directions (up, down, left, and right) based upon whether or not open paths are available or if they are blocked by walls. So at each new point, up to 4 next steps can be taken, further branching your path. At the very least, this can usually be limited to 3 options so as not to go back to where you just came from, but more on that later.
A more advanced version of this that allows you to programmatically “cheat” the system is to instead implement a Bidirectional Search. A Bidirectional Search is essentially BFS that simultaneously starts from the start point AND from the desired end point (hence the “cheat” -> start from the end) and then meets in the middle. Because the overall path is then “combined” at the midpoint where your “start” paths and “end” paths meet, the required iterations are effectively cut in half. If you have the memory to spare, this can really save on runtime depending on the complexity of the maze.
In both cases, it is EXTREMELY important to prune your dataset as you go so you don’t explode your memory usage and crash your computer (or at the very least reduce your iteration speed to a crawl). There are a number of ways to do this, depending upon the constraints of the problem, but some of these methods include the following:
- Track the existing path (coordinates that have already been traversed) to keep from turning back/getting stuck in loops. Don’t move to a space if “you” have already been there.
- Remove all paths at a given location where other paths have already reached that spot in fewer steps (retain only the optimal path at a given point).
- Apply various problem-specific path selection rules at each step (instead of just at the end), where certain rules are stated. For instance, a rule to select one path over another if multiple are found that can be completed in the same number of steps.
- For Infinite grids and/or paths that have “state” changes after being “walked across,” remove all records from state tracking that are left in a generic other/start condition to avoid blowing up your data set more than you already need to.
For more reading on BFS Maze solves in Alteryx, 2025 AYXxAoC #1 leaderboard finisher Andrew Merrill (@CoG) provides a great summary of this topic in his blog post here.
5. Circular Groups
Elves seem to have a tendency to get together and hang out or play games involving circles. “Moves” often require movement clockwise or counterclockwise around the circle before doing something. But how can you loop through your records that are not stored in a circular fashion? Begin by establishing a start point position and then putting (aka sorting) subsequent records in directional order (ascending/descending, clockwise/counterclockwise, left/right, etc.). From here, you can leverage the modulo “mod” function for “moves” along the circle by using the number of required moves and the number of members in the given circle. The remainder output of this function will then return a zero-based number that will allow you to determine the final “location” for your next action.
6. Register Hacks
There are a number of AoC problems that involve the manipulation of a set of register values based on operation instructions. While these problems often seem quite daunting at first, if you carefully read through the instructions, they usually aren’t actually all that difficult to “code” into Alteryx. The trick, however, is getting them to execute said instructions within a reasonable amount of time.
Oftentimes, this involves identifying subset sequences/loops within your instruction input that can be replaced by different algorithms in order to speed things up. Take, for example, the following instruction set:
- cpy b c
- inc a
- dec c
- jnz c -2
- dec d
- jnz d -5
Without getting into all the specifics of these functions, the result of this loop (jnz in this case is a jump instruction) is that register 'a' is incremented by the value in register ‘d’ times the value in register ‘b’ and then clearing registers 'd' and 'c' (resetting them to zero). If starting with huge numbers in these registers, normal steps would take forever to iterate through to add 1 or subtract 1 to a given register in a single iteration. Swapping “add” loops by converting them into multiply functions can potentially replace thousands or millions of iterations into a single iteration step.
Meanwhile, if possible, attempt to deconstruct what these entire code sequences are trying to accomplish in aggregate, rather than letting the code just run to completion as described. A great example of this is found in 2018 Day 19. Even Part 1 of this problem will take millions of iterations to run through to get to the end result. In Python, for instance, this can still happen rather quickly.
Unfortunately, in Alteryx, you will be waiting for days, if it ever completes at all.
Instead, recognizing what the program is doing (in this case, within 30 iterations building to a target number and then finding the sum of all divisors from 1 to N of that number), you can instead run a small number of iterations, and implement a couple tricks with generate rows and modulo functions to find all required divisors, filter and sum with a summarize tool and your workflow will run in under a minute (for parts 1 and 2). Sorting out what is being done, however, is not always so easy, which brings us to…
7. Pattern Recognition
When AoC questions ask for a crazy number of iterations, USUALLY there is a pattern to be found in how the data is being transformed.
Instead of running a billion iterations, run a sample set (1-10 thousand for instance) to see if the data “equilibrates” to a standardized transformation (always adds “x” after a certain number of iterations, or cycles through output strings, for instance). Then you can find the remaining number of iterations required to complete to determine the final outputs.
As mentioned previously, try to use patterns in the data transformation to determine what is actually being done. Rather than brute forcing your way to the finish line with the code the problem essentially tells you to build, see if you can’t build some shortcut workarounds instead in order to exponentially speed things up.
8. Prioritize Generate Rows & Multi-Row Formulas Over Iteration
As long as your computer has enough memory to handle the number of records required, this route is often much faster in Alteryx than cycling through iterations. The caveat being that only a single field can be manipulated at a time, which can require some very creative formulas.
Every once in a while, there are no patterns and there are no real shortcuts… you just have to iterate to the end. 2017 Day 22 is a frustrating example of this, where a solution can be achieved running in a language like Python in a few minutes, but in Alteryx, this is just one area where the software is lagging behind. Even with a very high-powered computer, I ran the straightforward BaseA solution I built to this problem in Alteryx, and my workflow ran for over 2 weeks straight before finally arriving at the end state solution 10 million iterations later. (Normally I would have stopped, but at some point, I just needed to know how long it would take.)
Here is a case where I need to provide a special shout-out to Andrew Merrill (@CoG) for finding a crazy way to actually implement subsets of generate rows and multi-row formulas to exponentially speed up this process and get it down to a couple of hours of run time. In our session at Inspire, he will go over what he did on this particular problem, and it’s also posted to the Community, so I won’t steal his thunder. Needless to say, it is a great example of the power of these particular tools.
9. Alteryx Spatial Solves
While there are some things Alteryx is horribly slow at compared to what could be written in code (like chugging through millions of iterations quickly, for example), there are other areas where Alteryx really shines… one area in particular is anything related to spatial analysis. If a question seems to be asking for a spatial-related analysis (areas/lengths/etc.), your first step should often be to work on converting the problem input into spatial objects so you can leverage Alteryx’s built-in spatial tool capabilities. There are several complex spatial analyses that Alteryx has simplified to a simple tool config or single formula string that can make a very complex computer science problem actually quite “easy” to solve in BaseA.
Last year’s day 12 problem is a great example of this. If you can’t make it to our AoC talk at Inspire this year, you can read about how I leveraged spatial tools to simplify this particular problem in the deck we will post after the conference. Top AYXxAoC solver and ACE Ippei Nakagawa (@gawa) also posted a great blog on using Alteryx spatial capabilities to solve the 2024 day 25 challenge that you can read about here.
10. Build Part 1 with Part 2 In Mind…
The more of these problems you do, the more you will learn how a given question will change for the second, often more difficult, part of the problem. Keep those techniques in mind while building your Part 1 solution, and sometimes you will get lucky with Part 2 and already have what you need completed. At the very least, you will be less likely to have to scrap everything and start from scratch with a completely different approach.
Well, readers, that’s it for now. If you made it this far, hopefully, you found at least a couple of these tips useful in how you could approach Advent of Code problems (or real-world challenges for that matter) in the future. For my fellow AYXxAoC compatriots, please don’t hesitate to add some of your own tips and tricks to the comments below if you don’t feel like writing a full-blown in-depth blog yourself. Hope to have you join us in December for year 11 of Advent of Code… in the meantime, happy solving!
