This week, our Friday presentations were all based around an Alteryx Weekly Challenge. My chosen challenge, #168, presented me with a scenario in which 3 6-sided dice would be rolled and their results calculated so that the two largest rolls would be multiplied and the final roll added afterwards for a final result. The question then, was which final result would appear the most. Fresh off of that, I’d like to share what I learnt in the two methods I used to get the answer, and the wonderful tool that is the Multi-Row Formula. I initially did this in a very “quick-and-dirty” method, but was also shown a method that could scale to a higher number of variables by Beth, so that in a hypothetical where it was 50 dice being rolled I wouldn’t be typing out code for a week non-stop.

Multi-row Formula Tool in Alteryx

The First Method

Very quickly, here is the formula I wrote in the “brute force” method after appending all 3 dice outcomes together into a singular table displaying all the possible outcomes of dice rolls. As you can tell, this only really worked because the question presented me with 3 dice and there being only 6 possible combinations of the order of magnitude by dice roll meant that adding even 1 more dice to this would double the lines of code needed.

The Second Method

Before I get into the magic that is the Row ID tool, there’s some slight reformatting that needs to be done which helps this whole process. Namely, using Record ID to assign a numerical value to each row that can persist across table transformations, Transposing it by this new column so that each RecordID is now in 3 rows, 1 for each dice roll, for all 216 possibilities, and then Ordering it by ascending RecordID and descending Value. As the Order tool respects hierarchy, it output a table where each RecordID is grouped together in its dedicated 3 rows, but the values are now ordered from highest to lowest.

Now we are ready for the Multi-Row Formula tool and though it was hard for me to wrap my head around at first, Beth showed me the light. Creating a new column labelled Dice ID (still grouped by RecordID), it’s been given the expression of [Row-1:Dice ID]+1. This was the cause of my confusion since I didn’t really understand how to read this at first but basically, each row in Dice ID column would look at the previous row and add 1 to its output. As this is still grouped by RecordID, it’ll simply repeat the numbers 1,2, and 3 for the 3 descending values of each unique instance of RecordID. Importantly, the option “0 or Empty” has to be selected so the first row of each RecordID doesn’t spit out a NULL trying to reference a previous row that doesn’t exist.

Final Clean Up and Presentation

With all that done, simply Cross Tab back the results grouping by RecordID and using the values of Dice ID for the new columns to get a table that shows every possible dice roll combination with each individual roll per RecordID ordered from highest to lowest. While the original table column names of “Dice 1”, “Dice 2”, and “Dice 3” are gone, thanks to the previous usage of the Sort tool, their data is still preserved, just ordered each row in a way that goes from highest to lowest roll labelled “1”, “2”, and” 3″, the values returned from the aforementioned Multi-Row Formula tool.

I then applied the formula [1]*[2]+[3], and since “1” and “2” are always the 2 highest results, this fulfils the method of calculation the challenge posed us in the opening question. Afterwards, simply┬ájust Summarize as done above for the other method and the same result is returned, this time however, scalable to any amount of variables asked!

Concluding Thoughts

While I think that Alteryx, in the limited capacity I’ve had with it, is a fantastic user-friendly programme that is extremely accessible, the simplest tools that you’d likely get introduced to first can lead to some workflows that are quite long or are very bespoke to specific problems. This for me was a great experience to learn something new in the Multi-Row Formula tool, as well as understanding a method that is more future-proofed and scalable, and is a form of best practice that I’m keen to continue learning about and implementing in my work.

Daniel Yam
Author: Daniel Yam