Applying Iterative Macros: Alteryx Challenge 313 Base Converter

The challenge asks the challenger to produce an app that will convert any Base 10 number to a base selected by an end user. For those unfamiliar, we work with Base 10 numbers in day-to-day life, but other bases exist; on one end we have binary that consists solely of zeros and ones. Base 6 includes numbers up to 5 before we get to 10 - read here not as 10 but one zero. I mention Base 6 as I remember we did a week of math in Secondary School in Base 6 as the teacher thought it would be "fun". The good news is it helped me wrap my head round the initial challenge I had vaguely remembered how we converted to Base 6 and a quick google confirmed the method.

Key to converting a number to a new base is dividing said number by the base and noting down the remainders and the quotient; for those confused by the jargon:

5 here is the quotient underlined in blue whilst the dotted orange line identifies the remainder. The remainder serves as the last digit before we iterate again with the quotient from the previous division - note the end of this process is a quotient that = 0 we will use this as a condition to stop iteration in Alteryx.

We can use the MOD function in the formula tool to get the remainder within its own field where the first value is to be divided by the second value (which will vary dependig on what the desired base outcome is). To get the quotient we can leverage the Floor Function.

Floor(Abs([Decimal]/2))*IIF([Decimal]/2>0,1,-1)

2 here is set up for binary but can be updated in a macro with an action.  The IIF clause is designed to deal with potential negative values that are sent through an app. Ensuring that we return a negative sign to the quotient if required.

Once we have established these values we can go ahead and set the macro flow up to keep iterating as required. The formula tool would look like this for binary:

The first key to developing an iterative macro is clear documentation of your macro output tools in order to have clarity on what will be recorded each iteration and what will be fed back into the loop.

We have to tell Alteryx that we intend for this to be iterative by going to workflow configurations by clicking on the canvas and navigating to the configuration pane. In the settings specifying iterative macro will allow for additional options to select in the interface designer.

Using clear labels on outputs will be useful for when the iterative macro settings are specified in the interface designer. This screenshot identifies what will be sent through the iterative macro again and is therefore the iteration input.

The iteration input is the original data-frame that goes into the macro and the unlisted output in this screenshot is the output that we can then use to create the converted number with concatenation.

The second key is considering the condition for which the iteration will stop. In this instance when we have a quotient that is = to 0 we tell the iteration to end by filtering it from sending anything to the iteration output, we can consider the loop finished.

Once testing is complete on the binary logic we can use interface tools to expand functionality and replace the value of 2 used in the calculations for binary to be select-able in the drop down menu.

Because we are going to need the base in three formulas - in the formula to calculate remainder, quotient and base (the column that specifies in the output what base we are converting to) we need three separate action tools.

Each action tool will be updating a different formula expression. The whole formula tool setup is as follows:

We are storing iteration number in order to sort the output later on and concatenate in the correct order, most recent iteration for each record first. Base allows us in our final dataset to have a column confirming the base that the numbers have been converted to.

At this point in the macro we can run it on bases less than 10:

However we have issues with bases over 10 that use letters to "fill in" for numbers that do not appear in base 10:

By building in a lookup table for bases greater than 10 we can replace remainders of 10 or more with the letter required:

With the join condition set to join remainder on the lookup table equivalent.

The final components of the challenge solution included adding a recordID as the input put multiple numbers in for conversion and when we finally summarize to concatenate we needed an original value to group by. I used recordID to keep the groups together and join the original number back on to the iterative macro output.

Here was the sample output for hexadecimal, with the data grouped by recordID and sorted in decending iteration order the remainders can be concatenated to return the desired number.

Below I attach full screenshots of my completed flow:

Looking back on my workflows only 2 days later I realize the importance of documenting workflows. Although the flow makes sense to me, it is poorly documented and not organized well enough for a simple screenshot to communicate what is operating. In the long run this is problematic when producing work for less technically literate stakeholders or passing on work to someone who is technically superior to you but has to spend needless time translating and then documenting prior work. As such I intend to be more disciplined particularly when working with more advanced flows that include interface tools as apps or include macros in the flow and document them as clearly as possible using a template such as the one below:

I intend to circle back to my workflow and write a second blog sharing a documented version of the solution and then compare the two and reflect on the clarity proper workflow documentation provides.