Community News
Community news, customer stories, and more!
Who Says Analytics Needs to be All Work and No Play?
@JoshuaB has posted this year’s AoC announcement blog, kicking off the 2025 Advent of Code challenge using Alteryx.
Before I get started, I would like to point out a couple of things. For one, other than a bit of MATLAB programming in undergrad (biomedical engineering, 20+ years ago), and some additional Visual Basic and SQL later in business school, I have never had any formal programming education. Any dabbling I did with R and Python was on my own. Furthermore, other than a few rounds of hands-on product training at Inspire, I am self-taught in Alteryx as I was the one onboarding it at my former company, and I had even less Community learning resources available to me than what exist today. My point being, with some perseverance and some critical thinking, anyone can do this… just ignore that “at most 15 seconds” part up there when it comes to running some of these problems in Alteryx…
Anyhow, last December, I took my first foray into Advent of Code for the 10 year anniversary of the event, and in true Alteryx fashion, proceeded to solve all problems in BaseA, one (problem, 2 stars) a day from the 1st through Christmas, earning all available 50 shiny stars for the year.
A few weeks into January I then proceeded to dive headfirst into the deep end and attempt the AoC ultra marathon of solving all prior years’ problems (the remaining 9 years and 450 stars’ worth) before the next round of AoC started in December of ’25!
Well, ladies and gentlemen, despite my computer literally blowing up on me mid-AoC Alteryx run and having to wait nearly an entire month for all replacement parts to get it fixed (luckily that was the month of Inspire so I got to take a nice break to prep and present a bit of this madness with some fellow AYXxAoC rockstars – @JoshuaB, @Samantha_Jayne, @Andrew-Merrill-CoG / @CoG and @mmontgomery)… as of mid-September, mission officially accomplished:
Earlier in the year when I reached the halfway point (right before Inspire), I decided to post a blog sharing some tips and tricks I had picked up based around some recurring themes I had noticed across my AoC “travels” thus far. This time, however, I wanted to try something different: highlight some of the fun, wonky things I got to build along the way in Alteryx to give you all an idea of the fun to be had, and, just maybe, get a few more of you to join us next month for Advent of Code Year 11!
Now, speaking of Inspire, for those of you who have made it to Alteryx’s annual conference, hopefully you have dropped in on at least one session of Obscura to witness the wild art of the possible (improbable? Insane?) that can be achieved with Alteryx. If you have had the distinct pleasure of doing so, you will no doubt have seen how some very clever ACEs have developed games within Alteryx (Pong, Frogger, various casino games, etc.). Well, as it so happens, throughout the course of 10 years of Advent of Code challenges, various problems present you with similar opportunities… so our theme today readers? Games.
Throughout the many years of Advent of Code, there are multiple times when you find yourself presented with various games that you need to “play.” Whether this occurs while hanging out with some elves, while lost in space, or while stranded in the ocean floating on a raft to pass the time with a crab you apparently befriended is another story. Whatever the case, it is usually to pass the time while you wait for the next step of some important mission or journey you are on. Regardless of what the strange reason might be, you are confronted with numerous opportunities to “code” several different games (in our case, into Alteryx) and run various play through simulations to solve the given problems at hand.
AoC Table Games
Jumping back just a couple years ago, an early problem presents users with a game with similar rules to poker called Camel Cards. Rather than being dealt a single hand of 5 cards, however, players are dealt several hands (1,000 actually!) along with a bid amount for each, which as a data input looks something like this:
Total winnings are then determined, factoring each hand’s bid amount along with the rank of each individual hand’s value relative to each other. A given hand of cards is ranked first by the type of hand (5 of a kind, 4 of a kind, full house, etc.) and then by a set of tie breakers defined within the problem related to the value of individual cards and the order in which they appear in the hand. Got it?
Okay, deal us in! After we do some simple parsing to separate out the bid value from the actual hand of cards in each record we can then score each provided hand by performing two main tasks: translate each card into a numerical value and evaluate the type of hand. All right, one step at a time, so let’s get started with assigning numerical values to each card position. While there are a number of ways to accomplish this task, knowing that I would later need to order (i.e. sort) hands based on the value of each card in each position, I decided to make use of the substring function which allows us to extract a set number of characters from a given string starting at a defined position. We can make use of a single formula tool to extract an individual string position and code it as a numerical value by throwing it into an IF statement:
Now that the individual card value translations have been taken care of, we just need to sort out the type of hands we are dealing with and rank them appropriately. Okay, time to start counting cards (if only we could get away with this in the casinos).
First, let’s separate each of the hands we were provided into individual cards (i.e. split to separate records), using the RegEx tool to tokenize our data and splitting to rows with the very simple regular expression “.” to separate on any single character. Next, let’s use a Summarize tool to group by each hand and type of card, and perform a count of each card. If we then perform a quick sort by the counts in each hand, our data stream starts to look something like this:
See where we are going with this yet? Hold tight, we’re almost there. If we convert our “Count” field to a string data type, we can then concatenate it, grouping by the HandID field, and are left with a representation of each hand type we can then rank:
Now that we have fields established for our hand type rank as well as the numerical value of each hand position’s card, a quick sort will arrange all thousand of the hands we were dealt into the appropriate rank order. Multiply this ranking we established by the bid we were provided for each hand, sum the total, hit run and a split second later we have our answer! Part 2 of this problem works much the same way but changes our “J” cards from Jacks to Jokers. The secret here is determining your hand type without the Jokers, the count of Jokers in the hand, and how to “upgrade” your base hand type to the best available hand given the number of Jokers. I’ll let you work that one out on your own… I can’t just give you ALL the answers 😉.
-----
If we go a few years further back, there was a time in 2021 where you find yourself slowly drifting to the bottom of an ocean trench in a submarine, so while you await the descent the sub’s computer challenges you to a dice game. This starts out simply enough, just a circular board game you take turns moving around and a 100-sided die you “roll” to move.
Luckily for us, this die is deterministic in that it lands in ascending order: 1 then 2 then 3… etc. until it hits 100 and then starts back at 1, making things much easier to build out in Alteryx. This however is just the practice game (Part 1 of the problem) that can be solved rather quickly with a simple iterative macro… so let’s skip ahead to the real game in Part 2. This time we are provided with what appears to be a simple 3-sided die. Only there is a catch: it is actually a quantum die that when rolled splits the universe into multiple copies, one new universe created for each version of each roll that could have possibly occurred on every single roll of a game.
Our task, meanwhile, is to determine in how many universes Player 1 defeats Player 2. Using the example board starting positions provided, we are informed that this occurs in a mere 444,356,092,776,315 versions of this multi-versal reality… Uh-oh… that’s a lot of simulations to run and/or records to generate, and I’d prefer not to blow up another computer. Fear not, friends, with a few tricks we can get this thing to run to completion in under a second!
With three rolls of a 3-sided die per turn, with each passing turn a given player has 27 (33) possible new rolls (and universe splits). However, the TOTAL of their rolls each turn and the subsequent number of moves they make is much less:
This gives us something much more reasonable to work with. We’ll still use an iterative macro to cycle through each turn, but now we only have 7 records to append to our dataset after each player’s turn starting with our initial positions and scores (occurring in the original SINGLE universe):
We can then keep track of the universe count that a given state (board position and score) occurs by multiplying the current universe count by the frequency value of each potential total roll with each iterative pass through our macro:
Based on the scoring rules of the game, if a player’s score reaches or exceeds 21 we output the record while continuing to iterate any universes (i.e. records) in which a game is still in progress. The other ESSENTIAL piece to this iterative macro is found in that Summarize tool right before the iterative Macro Output. Here, we sum all universes that have arrived in the same state after a given turn (player positions and scores) before passing onto the next iteration in order to limit our records we must continue to process (a critical step to MANY AoC problems):
With these two little record reduction tricks combined, we are able to count all (in my case) 56,852,759,190,649 universes where Player 1 is victorious (plus all the versions where Player 2 wins) in under a second!
-----
If you thought those games sounded interesting, be sure to take a look back at 2020’s Day 22 problem where you get to play an interesting game of Combat (I always knew this card game as “War” myself) against that crab I mentioned back near the beginning of this post. It begins in a fairly straightforward manner but then gets pretty wild as it introduces recursion rules to handle ties in cases where you and the crab flip the same card. Why break a tie with the next few cards when you could play an entirely new game with your remaining deck each time ?!?! But that’s enough table games for now… Be sure to tune in next time as we head to the AoC Arcade!
Curious and want to try some of this out yourself? Be sure to head over to AOC 2025 in the meantime to learn how to participate and get ready to join in on the fun as the calendar shifts over to December for Advent of Code 2025!
