The Abacus add in is a collection of custom functions that make writing expressions in Alteryx easier or gives completely new functionality (such as variables). Originally created to make my life easier, they have grown into a powerful extension for Alteryx that can speed up creation of complicated formulae. They focus in on a few key themes:
In general, the functions have come either from my own wants for easier ways to do things or more commonly from ideas when answering questions in the community. This document gives an overview of the collection as it stands at version 1.4 (which should be released very soon).
Please always remember when using any SDK tool or custom function in a workflow, that the add in will then need to be installed on any machine you wish to run the workflow on.
The best way to install the add in is to use the Analytic App Installer. This app should download from GitHub, then extract and install the add in into Alteryx. If it doesn't work, you can get the direct download following the instructions below.
The newest release is always available from GitHub. In each release you will find the following kind of structure:
The two zip files contain the actual add in (AlteryxAbacus.v.22.214.171.124.zip) and the set of test workflows (AlteryxAbacus.v.126.96.36.199.Tests.zip). The Manual.pdf contains a snapshot of the documentation wiki produced when the release was packaged. Finally, the source code is included as both a zip and a tar.gz file.
To install onto Alteryx Designer (or Server), you just need to download the main zip file. You can then extract it and run Install.bat. This should install the functions into Alteryx. To uninstall, run Uninstall.bat. These scripts have been tested on Windows 10 but will need administrator access to run. There is also a couple of Windows 7 compatible scripts (Install Win7.bat, Uninstall Win7.bat) included in the zip file.
These functions make working with and manipulating dates easier. They break into a couple of collections:
These return the specified part as a number. Some of the functions now have built-in equivalents.
It is often useful to be able to round a date down or up to the period start or end. These functions provide those capabilities:
These are some quick functions for building dates either from strings, numbers, or other datetimes:
Last few functions handle working day calculations and any other miscellaneous date tools I needed:
Based on the Boost library, these functions allow computation of values on various probability curves:
These functions help with the generation of fake data.
These functions don't really fit in any other category.
Functions to handle dealing with NULL easier.
A couple of functions to convert to and from Roman numerals (based on an idea from Ken Black)
This is a specialist function to allow for a simple VLookup style join. If you take a list of values in ascending order and join together into a comma separated string, then this function will return first value equal to or above the input value.
These are quite specialised but make development of new functions or advanced formulae easier.
Variables are brand new in version 1.4 and add a whole new dimension of capabilities within formula tool. You can do things like implement a Newton Raphson solver using the generate rows tool or create a grouped running total in a formula tool without resorting the data. They are very experimental so please use with caution. Documentation on these new functions is also not yet complete.
Variables come in 3 types:
Variables exist for the execution of a workflow and can be passed between tools. I have not done sufficient experimentation with macros to know how they play with these. The list of functions for working with variables is:
Interacting with a list of values requires some more functions. You need the ability to check the list exists, get the length, access a value, set a value, and delete a value. Indexes start from 0.
This whole project is open source and if you would like to contribute, I would value the submission. All of the code is open source and available on GitHub, below is a quick overview of the code and set up I use to develop the functions.
├── AlteryxAbacus │ ├── AlteryxAbacus.aps │ ├── AlteryxAbacus.cpp │ ├── AlteryxAbacus.h │ ├── AlteryxAbacus.rc │ ├── AlteryxAbacusUtils.cpp │ ├── AlteryxAbacusUtils.h │ ├── AlteryxAbacus.vcxproj │ ├── AlteryxAbacus.vcxproj.user │ ├── ChiSquaredDistribution.cpp │ ├── DateTimeFunctions.cpp │ ├── EngineVersion.cpp │ ├── EngineVersion.h │ ├── Generator.cpp │ ├── HexBins.cpp │ ├── LogNormalDistribution.cpp │ ├── NormalDistribution.cpp │ ├── resource.h │ ├── RomanNumerals.cpp │ ├── stdafx.cpp │ ├── stdafx.h │ └── StudentTDistribution.cpp ├── AlteryxAbacus.sln ├── AlteryxAbacus.sln.DotSettings.user ├── DateUtils.xml ├── MathUtils.xml ├── MiscUtils.xml └── StringUtils.xml
The XML files contain the function definitions (both for the macro functions and the C++ based ones). They are separated by category. The C++ code is all contained in a single Visual Studio project. In general, I again keep each set of functionalities in its own cpp file making them easier to handle.
In terms of tooling to work on these, you will need a reasonable text editor (I would recommend Visual Studio Code) and a copy of Visual Studio to work on the C++ code (Community edition is fine). In addition, I use the boost library (currently I use v1.67) which you will need to download and reference for the C++ code.
Testing is crucial when creating functions. I use the CReW macros to provide the List Runner macro. The following scripts and macros are in the repository to allow local testing:
├── Install - Debug.bat ├── Install - Release.bat ├── LinkDebug.ps1 ├── CreateSet.png ├── CreateSet.yxmc ├── ResultCompare.yxmc ├── RunUnitTests.ps1 └── RunUnitTests.yxmd
The Install - Debug.bat, Install - Release.bat and LinkDebug.ps1 will either install the output of a debug or release build into Alteryx. The LinkDebug script establishes a symbolic link allowing quick modification while developing. The RunUnitTests.ps1 script will run the RunUnitTests.yxmd workflow in all installed instances using the AlteryxEngineCmd.exe. This workflow seeks all workflows in folders ending with .Test in the folder name. In general, I keep a test workflow for each function in a folder named after the XML file containing the functions. For example, to test the StringUtils:
├── StringUtils.Test │ ├── ContainsTest.yxmd │ ├── EndsWithTest.yxmd │ ├── FindStringLastTest.bak │ ├── FindStringLastTest.yxmd │ ├── FromRomanTest.yxmd │ ├── LeftPartTest.yxmd │ ├── RightPartTest.yxmd │ ├── SplitTest.yxmd │ ├── StartsWithTest.yxmd │ └── ToRomanTest.yxmd ├── StringUtils.xml
A new addition in version 1.4 is a new workflow Coverage.yxmd which performs various static checks on the library. It looks for the following issues:
This workflow uses a lot of convention to ensure that the add in is well-tested and documented. By using this technique, I have found various issues that I had missed in previous versions. While I expect some work would be needed, the Coverage.yxmd workflow could easily be adjusted to check any custom function add in.
The last of the project consists of tools for packaging and releasing:
├── CreateRelease.ps1 └── vswhere.exe
These scripts are responsible for creating a new release package. This will locate Visual Studio and run a build of the C++ code in Release mode, before using the local testing set up to install and run the unit tests. Assuming this is successful, it will then create a zip file containing all the XML files and the compiled DLL file and the following installation scripts:
├── Install.bat ├── Installer.ps1 ├── Install Win7.bat ├── Uninstall.bat ├── Uninstaller.ps1 ├── Uninstall Win7.bat
These scripts allow for manual installation if the analytic app fails. The final steps of creating a release is to package up the test workflows into their own zip file and to create a PDF document based on the wiki pages (this is done using Pandoc).
Hopefully these functions are helpful, and the notes here give you some details on how to use them and how they have been built and tested. If you have any issues or questions, reach out to me on the community (@jdunkerley79) or raise an issue on GitHub.
Likewise, if you would like to contribute with new functions or code corrections, I will welcome the Pull Requests!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.