Overview
The Abacus addin is a collection of custom functions that make writing expressions in Alteryx easier or give new functionality. Originally created to make my life easier, they have grown into a power extension for Alteryx that can speed up creation of complicated formulae. They focus in on a few key themes:
- Date and Time
- Probability Distributions
- Data Generation
- General Utility
- Variables
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 function collection as it stands at version 1.4 (which should be released probably soon).
Please always remember when using any SDK tool or 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, 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.1.3.0.0.zip) and the set of test workflows (AlteryxAbacus.v.1.3.0.0.Tests.zip). The Manual.pdf contains a snapshot of the documentation wiki produced when the relase 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.
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.
-
DatePart(interval, dt): Replicates the SQL DatePart function, getting a specified part of the datetime input -
Century(dt): Gets the century for a date -
Year(dt): Gets the four digit year -
Quarter(dt): Gets the quarter of the date [1-4] -
Month(dt): Gets the month number for a date or datetime [1-12] -
Day(dt): Get the day of the month for a date or datetime [1-31] -
OrdinalDay(dt): Gets the day of the year [1-366] -
Weekday(dt): Gets the day of the week [Sunday (0) through to Saturday (6)] -
Hour(dt): Get the hour part of a DateTime or Time [0-23] -
Minute(dt): Gets the minute part of a DateTime or Time [0-59] -
Second(dt): Gets the second part of a DateTime or Time [0-59]
It is often useful to be able to round a date down or up to the period start or end. These functions provide that capabilites.
These functions are built on top the Custom Function SDK. There are two ways to create a new function - either in XML or in C++. All of the code is open source and available on GitHub. The code is structured as follows:
├── 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 functionality 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 downlaod 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
├── Coverage.yxmd
The last of the project consists of tools for packaging and releasing:
├── CreateRelease.ps1
├── Install.bat
├── Installer.ps1
├── Installer.yxwz
├── Install Win7.bat
├── Uninstall.bat
├── Uninstaller.ps1
├── Uninstall Win7.bat
└── vswhere.exe