CS Macro Dev: Iterative Macros

[AlexKo]

This article is part of the CS Macro Development Series. The goal of this series is to communicate tips, tricks, and the thought process that goes into developing good, dynamic macros.

When I started out at Alteryx, I completed a series of exercises to help me ramp up. One of these was the “knapsack problem” below:

There are 5 boxes of varying weights and dollar amounts - which boxes should be chosen to maximize the amount of money while still keeping the overall weight under or equal to 15 kg?

From a challenge standpoint, which combination of box/boxes is most optimal if you were only allowed 1 box in the backpack? 2 boxes in the backpack? 3 boxes in the backpack? or 4 boxes in the backpack?

This is a classic optimization problem. In Alteryx, the most straightforward way to go about investigating this problem would be to generate combinations of these items using a series ofAppend Fieldstools.

You would need one additional Append Fields tool for each number of objects you'dlike to investigate. The above creates 2-item permutations, and tacking on another Append tool to match up the original boxes to the output of the first Append Fields would give you 3-item permutations. You'dalso have to filter out the repeats in this case – after all you can'thave two of the same box placed in the knapsack. In the end, this method would require you to combine streams where you look at 1,2,3,4, & 5 box combinations. It wouldn'tbe a lot of work, but it also wouldn'tbe dynamic – that is to say, if we suddenly added a sixth box, our workflow wouldn'tbe able to handle it properly.

In computer programming, a recursive solution may be best – it would call upon itself until all combinations have been generated. For a number of reasons, recursion is not possible in Alteryx. However, the next best thing to recursion is a “looping” solution, and this is possible – in the form of an iterative macro. An iterative macro will run over and over until it either reaches a set number of iterations or until some logical condition is met. After an iteration has completed, its output may be looped back into the input of the macro for the next iteration.

In this case, we could use an iterative macro to essentially replicate the Append Fields logic as many times as needed. One-character length strings can be used to represent items, and concatenated strings to represent combinations.

A Filter toolbefore the macro'soutput would check each time to see if the length of the combinations was the same as the number of items. If it was the same, it would know that all appends have been completed and output the list of combinations to the “Done” anchor (Shown here with the letter "O"). If not, it would feed the results out of the “Loop” output ("L"), and run another iteration. Once no data was being filtered through to the “Loop”, the iterations would cease.

You don't need all the records to be "Done" all at the same time. You can output any number of records during an iteration. These results will just be stacked together at the end of the final iteration based on theOutput Modeyou have configured in theInterface Designerwindow(View > Interface Designer). TheInterface Designeris very important for configuring an iterative macro because you need to tell Alteryx which output it should loop back (Iteration Output), and which input should receive the looped output (Iteration Input).

You must also specify an upper limit on the number of iterations it can run so that you don'taccidentally create a macro that runs forever. For the Combination macro, I set this limit at 25, since at this point it will be generating 33,554,431 combinations… which is excessive.

I wrapped the entire iterative macro up in another macro so that I could include some configuration options and a Message tool to prevent more than 25 items from being fed into the iterative macro to begin with. A Count Recordsmacrois used to find how many records there are, and this number is appended as a field to the data. The Message tool is configured as "Error - and Stop Passing Records Through This Tool" in order to halt executionif the “count” field is too high.

The end result is the Combination macro. It'sable to take a field and return all combinations of the items inside.