Control + Alteryx + Repeat
The first release of Alteryx Designer in 2023 dropped last week, and I am excited to share with you some of the highlights of the new features and what they could mean for you. The most impactful features include Control Containers, six new formula functions, and sub-second precision datetime data types. Control Containers are a new concept, but will be a game changer for Alteryx developers, so I will talk about those first.
Control Containers – Control the Order and Execution of Workflows
The most intriguing new development is the advent of Control Containers, a new tool from the Developer Tool Category. They look and behave like regular Tool Containers but with a twist – they allow users to run workflow sections using conditional logic. If you say to yourself, “I only want this section to run if/when…” then Control Containers are a viable solution. This works by activating a Control Container after you pass the records to it; or deactivating a Control Container if you pass zero records to it.
There are multiple use cases for Control Containers. For example, you could only make a second GET request if the first GET request was successful like so,
If the first Download Tool fails, zero records will be passed to the second container, causing it to be deactivated and preventing the second Download Tool from executing: saving time and erroneous results. You could also connect the False Anchor to another Control Container to run a different set of tools if the first Download Tool fails.
I think the most significant potential is in ‘orchestrating’ the Output Data Tools. For example,
- You can only output data if a condition is met. You can use this to ensure that data sets are updated in a table only after passing QC checks. Or to only overwrite a file if you find the records in the source data.
- You can now output to multiple sheets of an Excel Workbook within the same workflow without the file becoming locked and getting errors because you are writing to the same file simultaneously. Where the Block Until Done Tool fails, you can add each Output Data Tool (for each Sheet) to a Control Container and link them together to ensure they execute consecutively and without error. This will also ensure that your sheets are in the correct order!
- You can even output a data source and read that data within the same workflow. And you can force the Input Data tool to run after the Output Data tool.
Another thing I love is the use of Control Containers to output log messages as a data stream (as opposed to messages in the results pane that are liable to go unread). At a minimum, the output of an enabled Control Container will have two records with notes: Control Container Activated. and Control Container Completed.,
But what’s more valuable is that the output will also show messages from other Alteryx tools within that Control Container, which can be particularly useful when using Message Tools for custom messages,
Putting these messages, warnings, and errors into a data stream allows us to output these messages to any data source making these potentially critical messages visible outside of the workflow to non-Alteryx users. A common question from businesses is, “How do I know the workflow is doing what I want it to do?” This feature will help add transparency for workflow auditors.
New Formula Functions – Adding to the Function Menagerie
New functions are always welcome; the latest 2023.1 update blesses us with six more to simplify calculations.
AverageNonNull
We already had the Average (n1, …) function to take the average of multiple numbers, but this function assumes nulls are zeros, which for most purposes would be incorrect.
The AverageNonNull(n1, …) solves this issue by ignoring the null values. For example, the values 1, 2 and Null() would yield an AverageNonNull of 1.5,
Between
The Between(x, min, max) is a convenient way of determining if a number is between two limits. This could also be achieved by,
[x] >= min AND [x] <= max
But it’s a welcome simplification. For example, the function Between([n], 2, 8) would produce the following results,
The Between function will work for string data types too.
FLOOR and CEIL
The FLOOR (x, [mult]) and CEIL (x, [mult]) have changed slightly from the previous version in that now you can select an optional parameter that rounds down or rounds up to a specific multiple – similar to how the Round (x, mult) function operates.
For example, the functions FLOOR([n], 100) and CEIL([n], 100) round down to the nearest 100 and up to the nearest 100, respectively, and yield the following results,
Factorial
Factorial is an excellent new addition, particularly when calculating combinations and permutations. Previously, calculating the factorial of a number would require something akin to an iterative macro, which is somewhat overkill. Now, it couldn’t be simpler. For example, the function Factorial(n) would yield the following results,
Use the largest integer data type (int64) for these new fields, as these numbers get big quickly!
ToRadians and ToDegrees
Finally, two functions ToRadians (x) and ToDegrees (x), make it easier to convert between radians and degrees. Although this was possible by multiplying and dividing by π/180, respectively, it is now simpler. Note that the ToRadians function benefits the trigonometric functions (e.g., SIN, COS, TAN, etc.) that take radians (not degrees) as their argument.
High Precision DateTime
Alteryx handles Dates and DateTimes very well. But until now, there has been a hard limit of per-second precision, and some applications require sub-second precision (think McClaren’s Formula 1 lap times or financial trade execution times). The DateTime and Time data types can accommodate up to 18 decimal places in seconds!
This is precision down to the “attosecond” (1E-18 seconds). Furthermore, we can do all the normal DateTime operations (e.g., DateTimeAdd, DateTimeDiff, DateTimeFormat, etc.) at this precision. Just be aware that the field size is large enough to accommodate those extra decimal places (and not the default 19 for regular DateTime data types).
We can also generate the current DateTime with a precision up to seven decimal places using the DateTimeNowPrecise(n) function (where n is the number of decimal places after the second).
For example, DateTimePrecise(7) would yield the following result,
And as mentioned, you can use this with the DateTime functions to deal with times down to the attosecond. For example, to add one attosecond, you could do something like,
DateTimeAdd([Field1], 1, ‘attosecond’)
This new capability, compatible with the core DateTime functions, will be invaluable for those regularly dealing with sub-second times.
The Intelligence Suite
The Intelligence Suite has gotten a few nifty updates, such as a new Text Classification Tool and enhanced and auto-alignment steps in the Image Processing Tool for scanned documents. But, for my money, the most useful addition is the ability to apply the PDF template annotations to multiple pages, which is a common ask for clients wishing to scrape every document page that follows a strict format.
This option is a checkbox that can be found at the bottom of the PDF to Text or Image to Text Tools,
Other Notable Mentions
- Dark Mode is now available in beta via the Customization tab of the User Settings.
- The case of field names will propagate downstream without manual configuration.
- The S3 Upload and S3 Download Tools now support AWS IAM roles.
Alteryx Designer 2023.1 has added some key new features to make the Alteryx Developer’s life easier. Although nice-to-haves, most of the latest formulas are shorthand for what is already possible, few businesses require sub-second precision, and most people do not care about having a dark mode. Conversely, Control Containers will have a significant impact as they not only tackle the issues and errors incurred from tools running in an undesirable order but also add transparency to how the workflow runs by outputting the log messages useful for businesses that require more thorough audits of their workflows.
