Version 1.1 of my Alteryx formula add ins project has been released. These provide a set of utility functions for use wherever a formula can be used within Alteryx. The functions are developed in C++ and XML using the Alteryx SDK – I will write a few posts with the details of how to create a new function and how the unit test framework works in the next couple of weeks.
Goal of the Project
The idea of this project is to:
- Provide a set of useful functions to make data preparation and analysis simpler and easier.
This project started to make string filtering easier within version 9 with the creation of three simple XML based functions:
These are now part of version 10, and commented out in this project
Over time, have expanded functions based on issues that come up in the community, particularly around handling dates.
- Increase exposure to the formula add in SDK.
Alteryx provides a great SDK for creating both custom tools and new formula. Part of the goals of this project is to create a set of sample XML and C++ functions people could use as basis to create their own.
Alteryx looks for XML files in a folder called FormulaAddIn within the RuntimeData folder inside the bin directory of the Alteryx install. For an admin install, this will default to be: C:\Program Files\Alteryx\bin\RuntimeData.
There is an Install.bat script which will copy all the XML files and the DLL files into this folder. As Windows UAC protects this folder if within Program Files, the install script will ask for permission to install. The script will also unblock all files, as Windows will possibly have blocked them as they were downloaded from the internet.
If you have a non-admin install of Alteryx, it is possible the install script won’t be able to find the Alteryx folder. In this case or if the bat file fails you will need to copy the files manually.
To do this, copy all the XML and DLL files to %AlteryxBinDirectory%\RuntimeData\FormulaAddIn.
A few additional general methods for data preparation.
- IfNull: If first value is null return second
- Coalesce: Given list of values return first non null value (C++)
Math based functions that I haven’t got a better home for!
- Modulo: General Double Based Modulo function
- HexBinX: Given an X,Y point and optional radius, get X co-ordinate of hexagonal bin’s centre
- HexBinY: Given an X,Y point and optional radius get Y co-ordinate of hexagonal bin’s centre
- Rand_Triangular: Given a uniform random number transform into a triangular distributed random
- Avg: Average of a list of values ignoring NULL
- Count: Count of a list of values ignoring NULL
- Sum: Sum of a list of values ignoring NULL
Some additional functions for working with Dates and to a certain extent Times.
Date Time Creation Functions (Currently Just Concatenation)
- MakeDate: Create a new date from Year, Month, Day
- MakeTime: Create a new time from Hour, Minute, Second
- MakeDateTime: Create a new DateTime from Year, Month, Day, Hour, Minute, Second
- ToDate: Truncate a DateTime to a Date
- ToDateTime: Appends midnight to a Date to create a DateTime
Date Part Functions (returns numbers)
- Day: Get the day of the Month [1-31]
- Month: Gets the month number [1-12]
- Year: Gets the four digit year
- WeekDay: Gets the day of the week [Sunday (0) through to Saturday (6)]
- Quarter: Gets the quarter of the date [1-4]
- OrdinalDay: Gets the day of the year [1-366]
Period Start and End
- WeekStart: Get first Sunday before or equal to date
- WeekEnd: Get first Saturday after or equal to date
- MonthStart: Get First Day of Month
- MonthEnd: Get Last Day of Month
- QuarterStart: Get First Day of Quarter
- QuarterEnd: Get Last Day of Quarter
- YearStart: Get First Day of Year
- YearEnd: Get Last Day of Year
Other Date Functions
- DateAdd: Equivalent to DateTimeAdd but returning a Date
- BusinessDays: Number of weekdays between two dates
- IsLeapYear: Is a year a leap year
(takes a year not a date as an input use IsLearYear(Year([Date]))
A few extension methods for working with strings.
- LeftPart: Gets the text before the first instance of a separator
- RightPart: Gets the text after the first instance of a separator
- Split: Splits a string into tokens and then returns the specified instance
Retired Functions (built into v10)
- StartsWith: Does a string start with another
- EndsWith: Does a string end with another
- Contains: Is a string in another
V1.1 Release Notes
- Full set of unit tests for all the functions.
- Wiki now contains a function reference for all the functions.
- Improved Install.bat to pick up all xml and dll files.
- New functions: AVG, COUNT, SUM, MAKETIME, MAKEDATETIME, TODATE, TODATETIME, DATEADD
- HEXBINX and HEXBINY moved to C++ functions
- Bug Fix: LEFTPART and RIGHTPART missing bracket.
- Bug Fix: RIGHTPART return whole string if delimiter not found.
- Bug Fix: MODULO deal with NULL or 0 for divisor.