Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
ned_blog
8 - Asteroid

Writing macros is like writing any other code. You have to get into a programmer mentality. Often you write a module completely differently when writing it for a macro instead of solving today's problem. I am going to demonstrate by starting with a customer feature request for Alteryx.

Let's start with a real feature request from a real customer:

Hi Tara,
I have a table with 60+ fields. I need to create an extra field that is the total of all the 60+ fields. So far, I've been doing this with the formula tool and writing an expression as follows "[01]+[02]+[03]+[04]+[05]+[06]+[07]+[08]+[09]+[10]+[11]+[12]+[13]+[14]+[15]..... etc". Just wondering if there is a quicker way to do this? If not, can you add as a suggestion for a future release?
Thanks,

 

Write a Specification/Requirements

 

What is the customer asking for? How many inputs? How many outputs? It doesn't have to be big or even too detailed. But it is a good exercise, because it really forces you to understand what is required. I usually do specs as outlines.

 

Requirements:

    • A single input
      • No field map. There are not specific named input fields required.
    • A multi-select list box to select fields
      • Limit to numeric field types
    • A single output
      • Passes through all incoming fields
      • Adds a single field named Total

Solution

    • A formula tool with a dynamically generated formula
    • A question with a list of fields to Sum
    • An action to create and update the formula.

As you can see, we are not proposing a very large solution. Often, problems that are complex for the user are simple when developed into a macro.

 

Create a prototype

 

This should be a very simple module that accomplishes the solution. The 1st thing to do is to find some data that approximates the request. I took a bunch of data from Allocate. I added the formula tool, but I didn't add all the data. I can see why the customer wanted this feature, it is a pain.

 

Tip: In order to avoid having dependencies for your macro, copy the prototype data from a browse into a text tool in the module. After copying from the browse, just right click on the module canvas and paste.

 

Note: If you are going to be using input mapping (we're not in this case), make sure to have the minimum number of fields and have the field names be consumer friendly.

 

Make it a macro

 

When creating the macro inputs, we have to make sure to check the "No Field Map" option for the macro input. Since this macro doesn't rely on specific named fields it makes a nice clean interface for the user. In general, the field map should only be used for quick one-off macros. The field map is great for making macros quick to develop, but users don't always want their fields to be renamed and reordered, which is a side effect of using it.

 

In this case we have a multiple select list box for the fields to sum. We allow the user to only pick numeric fields, we default to all numeric fields on. The only tricky part is we use the "Generate Custom List" mode to create the entire formula.

 

 

Since the Custom List made the expression just the way we needed it, the action is easy. We just need to feed the value from the list box to the formula.

 

Set an Icon

 

This is a purely cosmetic thing, but your tool will feel like a built in tool to your users if it has an icon. Often times you can find something copyright free on a Google image search. In this case, I just took the ? Unicode character and created an image with paint.

 

Anticipate what they are going to want next.

 

You thought you were done right? You solved the exact problem your customers (users) ask for, so they should be happy, right? Well, inevitably they are going to say thanks, but why doesn't it do X?

 

Your customers might ask for more summarization methods, like average, Min & Max; so let's add a new question with a method and spruce up the action. Its challenging to get your head around the action now, because we are now writing a formula that generates a formula. The potential for syntax errors is high here.

if [Method]=="Sum" then
   [SumFields]
elseif [Method]=="Average" then
   "(" + [SumFields] + ")/ " + ToString(1+REGEX_COUNTMATCHES([SumFields], '\+'))
elseif [Method]=="Min" then
   "Min(" + Replace([SumFields], ']+[', '],[') + ")"
else //if [Method]=="Max" then
   "Max(" + Replace([SumFields], ']+[', '],[') + ")"
endif

 

Finally, I added 1 more action to change the field name on output.

 

Test!

 

I can't say enough about testing the final macro when you are done. Make sure all the summarization methods work and produce the correct output. Run it in a bunch of different ways. 30 minutes of testing at this point will save you and your users hours of time later.

 

Pending review by my team, I think I have a macro that is up to the standards of shipping with Alteryx as a feature for the next version.

 

The modules for this blog can be downloaded here.