Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Building your workflows with strategy will take them to the next level and increase your skill. This ability to analyze context and identify the most important considerations and risk factors is transferable across all technology contexts. Alteryx Designer is a great way to learn these principles since the Designer canvas makes it so easy to visualize.
Additionally, building with strategy is the current human value-add for coding. At this time, AI can write functioning code, and soon, it will be able to write great code. However, it’s up to you to recognize important, non-quantifiable factors like the personality of your stakeholders and risk factors in your industry. Until AI can analyze your entire corporation, industry, and work life, building with workflow strategy will remain a human value-add.
That being said, there are three common strategies I have identified over the last few years. They’re not the only three, but they should give you a foundation for starting to think with this framework.
I have aggregated tips and resources for learning more about the strategies of generalizability, scalability, and maintainability. There are key questions that you can use to analyze your everyday workflows through these perspectives. Without further ado, let’s start with generalizability.
Generalizability
Also known as: dynamic, generalization, futureproofing
Key Questions
Part 1: If my _____ were different, would my workflow still function?
Fill in the blank with context from any given configuration in your workflow. Columns, row order, file name, directory, time of year, user input, etc.
Part 2: If my workflow breaks due to _____ being different, what is the impact? (Thank you, @grossal, for this question!)
Answering these questions will guide and focus your efforts on which configurations are the most important to make dynamic.
Helpful Tools
Some tools within Alteryx Designer especially facilitate building dynamically. Some of them have “dynamic” in the name, but they all relate to conditional logic or taking values from fields.
- Dynamic Select: Documentation, Tool Mastery
- Dynamic Rename: Documentation, Tool Mastery
- Dynamic Input: Documentation, Tool Mastery, SQL Article
- CReW dynamic tools (Dynamic Formula, Dynamic Metadata)
- Tools with expression editors
- Tools that have “Dynamic or Unknown” options
- Build a macro: Documentation, Article, Video Lessons
- Input Data * Wildcard
- Concepts from vacation-proofing series
Tips & Considerations
Data Sources
-
Avoid hardcoding. Hardcoding means to type a value directly into your workflow, typically in an expression. Instead of hardcoding, store values in variables (text inputs, fields, and/or workflow constants) or user inputs (any of the interface tools).
-
Seek the original source. Start asking questions about your data inputs.
-
If your input is a system-generated PDF with a table, ask: is there a CSV extract instead? Is there a way to query the source system directly?
-
Is the data stored somewhere already, and if so, how can you access it (i.e. database or third-party API)?
-
If it’s a manually compiled Excel workbook, ask: where does the source data come from? Would it make sense to assemble the source data in Alteryx instead? The source data is less likely to change formats and is more automation-friendly.
-
-
Split data gathering logic and data processing logic into different workflows. In workflows expected to serve for many weeks or months, there are likely to be changes regarding the data source. If your processing logic is tangled together with your sourcing/gathering logic, any small source change will create a maintenance nightmare. Instead, breaking your logic into workflows by the following phases will prevent the maintenance nightmare. (Thank you, @SeanAdams, for this tip!)
-
Raw sourcing. Connect to the variety of sources to gather data.
-
Standardize. Clean up the names of fields in an explicit next step. Remember, you’re protecting yourself from changes in the source data. Split this out rather than burying it in your sourcing workflow.
-
Business Logic. Now that you have standardized data, you can move to the final phase for all your enrichment and business logic as usual. It should never need to change when your raw sources change, as long as you conform any new sources to look the same.
-
-
Be intentional in your use of relative, absolute, and UNC paths (Article). From the beginning, start to consider how this workflow will be used in the future. Will you ever need to send it to a colleague? Are the dependencies packaged with the workflow (use relative paths) or on a network drive (use UNC or absolute paths)? Will you ever load the workflow to the Server (use UNC or relative paths)? See the linked article for more details on when to use each type.
|
Path Type |
Example |
|
Absolute |
C:\Users\username\Desktop\subfolder\file.yxdb |
|
Relative |
.\subfolder\file.yxdb |
|
UNC |
\\fileshare\\subfolder\file.yxdb |
-
Write careful SQL queries that consider generalizability. Your Alteryx workflow’s generalizability can be greatly hindered or helped by the quality of your source query. For example, if you have a SQL WHERE clause that filters for the current year with a hardcoded ‘2024’, you’ll have to manually change it next year. However, you can make this generalizable by replacing the hardcoding with calculating the current year in an expression like the example below. (Thank you, @cplewis90, for the example!)
|
Hardcoded Query Example |
Generalizable Query Example |
|
SELECT ORDER_NUMBER, DATE, YEAR FROM DB.SCHEMA.TABLE WHERE YEAR = ‘2024’ |
SELECT ORDER_NUMBER, DATE, YEAR FROM DB.SCHEMA.TABLE WHERE YEAR = DateTimeYear(GetDate()) |
Conditional Logic
-
Use conditional instead of positional calculations. Ideally, your data source’s column headers are in row 1, and that is the only positional assignment you will need. Tools like Select Records and Unique use positional operations to determine which records to keep and remove. “Positional,” meaning based on record or column order. Instead, try basing your selections on characteristics of the data by using tools like Filter and Summarize.
Apply the key question here: what if your row order were to change? For example, compare these two configurations. They would give the same output!
|
RecordID |
Type |
Positional |
Conditional |
|
1 |
A |
|
|
|
2 |
A |
||
|
3 |
A |
||
|
4 |
B |
||
|
5 |
B |
||
|
6 |
B |
||
|
7 |
C |
||
|
8 |
C |
||
|
9 |
C |
Be very careful with the Multi-Row Formula tool as well—don’t simply assume your data will be in the correct order. Be sure to expressly add a Sort before the Multi-Row and use compatibility mode if using AMP Engine. In general, expressions can be conditional and dynamic or positional and brittle.
-
Be intentional with your use of “Dynamic and Unknown Fields.” Do you really want to allow additional, unexpected fields to pass, or would your workflow make more sense with the exact columns you have specified? Both configurations can be valuable, but I want you to be intentional with your choice.
If there’s any possibility of future new fields conflicting with current field names/renames, be careful. If you are writing a system output that should only ever contain the exact same five fields, consider deselecting “dynamic and unknown” right before the output. If you are regularly adding new fields, limit your removal of “dynamic and unknown” to one or two key locations. Otherwise, you will spend a lot of extra time threading and pushing new fields through your workflow.
-
Use expressions to assemble and modify file paths. Check out the file functions available in Designer. They are very powerful and significantly more dynamic than parsing filepaths using string functions.
The following screenshot shows my favorite dynamic way to add a datestamp to my output filename. I prefer DateTimeStart() over DateTimeNow() because DateTimeStart() remains constant across all tools, even if the runtime were to extend past midnight.
-
Orchestrate workflows conditionally using Control Containers (supported in Designer version 23.1 and later using AMP only). This makes workflow orchestration significantly easier! Now, you can officially instruct your workflows to run certain sections based on the outcomes of prior sections.
There’s already a CReW macro for running containers conditionally depending on whether the previous container finished without an error. Download CReW Control Container Error Check here (instruction video is on the same page). Article with more details.
Macros
-
Use a standard macro for recurring tool patterns (Article). If you need to modify that tool pattern later, if it’s contained in a macro, you can make the modification a single time to impact all workflows at once.
-
Use a batch macro for the amazing dynamic power to update any configuration with a value from a field (Article, Grouping vs Control Parameter). Since you can even customize the entire tool’s XML configuration from a field value, the dynamic possibilities here are endless. Note that batch macros are notoriously slow to scale due to the engine running a single batch at a time. You’ll have to weigh the importance of generalizability vs scalability. To help with scalability, keep the number of batches small and avoid processing each row more than once.
The following screenshot shows one possible configuration for updating an entire tool’s XML from a field.
-
Avoid the action tool’s configuration of “update specific part of a string” wherever possible. I prefer to replace the entire string or dynamically update the string using a formula.
Conclusion
Incorporating the principle of generalizability into your workflow will make it more robust and prepared for the future. By thinking through the future possibilities now, you’ll thank yourself later for all the extra work you saved yourself!
Determining when to use generalizability is a judgment call. It is possible to spend too much time generalizing. I recommend focusing your efforts using Key Question Part 2:
If my workflow breaks due to _____ being different, what is the impact?
Also check out the workflow strategies of scalability and maintainability (coming soon). These strategies are best utilized together! To return to the larger article containing practical tips to advance from user to superuser, click here.
