Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
In the face of a challenge, I have found that I like choosing the easy route. The tip that I’m about to share with you is not only easy to implement, but it also is elegant and provides for data governance and simplification of testing. In this case, the easy method is likely the best method. The challenge at hand is to take a known set of data (a fixed set of data elements) through a known set of data computations (a fixed set of formulas) to generate a known set of variables. One use case for this would be the data preparation in front of executing a model. When you’ve got a known set of data you might have a set of standard calculated data elements (e.g. Age in Months or maybe binary flags like Interest in Gardening). Let’s say that you have 100 or more of these calculated variables in your organization. What are the chances that everyone will use the same formula to compute the data? What are the chances that they will configure their workflows correctly without typos or cut/paste errors? In my experience, this effort is substantial. Let’s see how we could simplify the configuration of tens, hundreds or thousands of variables.
One possible way to get all of the formulas into your workflow would be to configure your formula tool via saved expressions. For those not familiar with saved expressions, please see Alteryx Help (Saved Expressions). These are terribly convenient and worth learning how to manage. But they still require you to configure formula tools with the potential for error. I am going to point you to a CReW (www.chaosreignswithin.com) macro for my tip. The macro of interest is: Dynamic Formula. The dynamic formula tool allows you to dynamically create formulas using a data feed. It effectively allows you to access a repository of stored formulas. Everyone creating a known variable will use the identical logic and virtually no coding is required for any of the formulas. In our use case of 100 formulas, once you filter to the set of formulas that you’re interested in calculating, the answers are ready for downstream processing.
The real effort is the initial configuration of each variable’s formula and the subsequent testing of the formulas. Once the calculation is proven to be accurate, it will remain accurate. The expectations for these stored formulas are that they have a known set of input variables and that the output calculation is fixed. Please note that if you wanted to have bespoke (custom) formulas, you can manage them and use them as you see fit.
In the example workflow below we see "Raw Data" coming into our model build. Sometimes there are values and sometimes not. A second input contains the median value (from which the original model was calculated) for use when a data element has a Null value. These "Slug" values are to be used to replace Nulls if required in the formulas. The slug values are appended to each input data record. The third input tool contains a set of formulas. W_Income is defined as [Income] divided by 1,000. If Income is Null, then the slug value is used. When the dynamic formula tool reads the formula, it applies it to the data and materializes a new W_Income variable in the output. It repeats the formula calculations for all fields contained in the library against the set of data.
Please do take a look at the picture below or if you've got a license for alteryx you can download the attached workflow and play with this yourself. If you don't have a licensed copy of Alteryx, here is a link where you can download a 14-day trial (Free 14-Day Trial). A special thank you to @AdamR_AYX for creating and maintaining this very useful tool. Please provide me with feedback on this post to learn more about dynamic formulas. I'll be at Inspire in San Diego and might also share in a future post how you could replatform your legacy models into Alteryx.
This workflow was created using Alteryx version 10.1
Please note that the attached workflow(s) make use of the "Dynamic Formula" macro available at www.chaosreignswithin.com. It is also present in the yxzp attachment.
Get Inspired at the May 4th Hangout
May the Force be with you!
Mark
