Skip to content

Overview

Jump to bottom Edit New page
James Dunkerley edited this page Apr 2, 2019 · 15 revisions

An Overview of the Alteryx Abacus Add In

Abacus Logo

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:

  • Date and Time
  • Probability Distributions
  • Data Generation
  • General Utility
  • Variables (new in v1.4)

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.

Installation

Analytic App Install

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:

Release 1.3

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 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.

Date and Time

These functions make working with and manipulating dates easier. They break into a couple of collections:

DatePart: Gets part of a date or time

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]
  • WeekNum(dt): Gets the week number of a Datetime, with Sunday as start of week and January 1st in Week 1 [1-53]
  • 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]

Period Start and End: Gets the beginning or end of specified periods

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:

  • YearStart(dt): Get First Day of Year
  • YearEnd(dt): Get Last Day of Year
  • QuarterStart(dt): Get First Day of Quarter
  • QuarterEnd(dt): Get Last Day of Quarter
  • MonthStart(dt): Get First Day of Month
  • MonthEnd(dt): Get Last Day of Month
  • WeekStart(dt): Get first Sunday before or equal to date
  • WeekEnd(dt): Get first Saturday after or equal to date

Parse and Create: Shorthand to make dates from strings or numbers

These are some quick functions for building dates either from strings, numbers, or other datetimes:

  • DateFromDMY(DMY): Parse a string in Day Month Year format to a Date (copes without leading 0s and different separators)
  • DateFromMDY(MDY): Parse a string in Month Day Year format to a Date (copes without leading 0s and different separators)
  • MakeDate(Year, Month, Day): Create a new date from Year, Month, Day
  • MakeDateTime(Year, Month, Day, Hour, Minute, Second): Create a new DateTime from Year, Month, Day, Hour, Minute, Second
  • MakeTime(Hour, Minute, Second): Create a new time from Hour, Minute, Second
  • ToDate(dt): Truncate a DateTime to a Date
  • ToDateTime(dt): Appends midnight to a Date to create a DateTime
  • ToTime(dt): Get the time from a DateTime, Date (defaults to 00:00:00) or a Time.

Other Date Functions: Business days and other bits and bobs

Last few functions handle working day calculations and any other miscellaneous date tools I needed:

  • DateAdd(Date,i,u): Equivalent to DateTimeAdd but returning a Date
  • BusinessDays(StartDate, EndDate): Number of weekdays between two dates
  • Workday(dt,days): Add or remove a specified number of weekdays to a date (similar to Excel's Workday function but no support for holidays)
  • IsLeapYear(Year): Is a year a leap year (takes a year not a date as an input use IsLearYear(Year([Date]))

Probability Distributions

Based on the Boost library, these functions allow computation of values on various probability curves:

  • ChiDist(X, DegreesOfFreedom): Compute CDF on Chi Squared distribution
  • ChiInv(P, DegreesOfFreedom): Compute inverse CDF on Chi Squared distribution
  • LogNormDist(X, Location, Scale, Cuml): Compute PDF or CDF on Log Normal distribution
  • LogNormInv(P, Location, Scale): Compute inverse CDF on Log Normal distribution
  • NormDist(X, Mean, StDev, Cuml): Compute PDF or CDF on Normal distribution
  • NormInv(X, Mean, StDev): Compute inverse CDF on Normal distribution
  • TDist(X, DegreesOfFreedom): Compute two tailed Student T distribution
  • TInv(X, DegreesOfFreedom): Compute inverse two tailed Student T distribution
  • RandTriangular(Prob, Min, Mode, Max): Produces a random number from a triangular distribution

Data Generation

These functions help with the generation of fake data.

  • RandomIPAddress(CIDR): Generates a random IP address, can be restricted to be within a CIDR block
  • RandomItem(Item1, Item2, Item3): Picks a random item from the input list (can be list of numbers or list of strings but must be one type)
  • RandomString(Pattern, CharSet1, ...): Generates a random string using a template to control format

General Utility

These functions don't really fit in any other category.

Handling Nulls

Functions to handle dealing with NULL easier.

  • Coalesce(Val1, Val, ... ): Given list of values return first non null value (C++)
  • IfNull(Val1, Val2): If first value is null return second

Geometry

  • Deg(Radians): Convert radians to degrees
  • Rad(Degrees): Convert degrees to radians
  • HexBinX(X, Y, R): Given an X,Y point and optional radius, get X co-ordinate of hexagonal bin's centre
  • HexBinY(X, Y, R): Given an X,Y point and optional radius get Y co-ordinate of hexagonal bin's centre

Aggregation Across Columns

  • Avg(Value1, Value2, ...): Average of a list of values ignoring NULL
  • Count(Value1, Value2, ...): Count of a list of values ignoring NULL
  • Sum(Val1, Val, ... ): Sum of a list of values ignoring NULL

Roman Numerals

A couple of functions to convert to and from Roman numerals (based on an idea from Ken Black)

  • ToRoman(Value): Given a number between 0 and 5000, convert to Roman numerals
  • FromRoman(Value): Given Roman numeral convert to a number

RangeJoin

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.

  • RangeJoin(Value, RangeCSV): Finds the first value in the RangeCSV parameter which is greater than or equal to the Value argument.

General Mathematical Functions

  • Int(Value): Rounds a number to nearest integer either equal to the number or closer to 0.
  • Modulo(Dividend, Divisor): General Double Based Modulo function
  • Quotient(Dividend, Divisor): Returns the integer part of a division
  • Sign(Value): Determines the sign of a number (-1 if less than 0, 1 if greater than, 0 otherwise)
  • Phi(): Returns the golden ratio constant.

General String Functions

  • FindStringLast(String, Target): Gets the position from the left of the last instance of a term in a string
  • LeftPart(String, Separator): Gets the text before the first instance of a separator
  • RightPart(String, Separator): Gets the text after the first instance of a separator
  • Split(String, Delimiter, Index): Splits a string into tokens and then returns the specified instance

Developer Functions

These are quite specialised but make development of new functions or advanced formulae easier.

  • Version(): Gets the major and minor version of the Alteryx Engine as a number
  • LogToFile(ReturnValue, FileName, Message): Write a message to a log file
  • ReportError(Condition, Message, ReturnValue): Raise an error from a formula if a condition is met, otherwise return a specified value

Variables

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:

  • Number (stored as a double but can be read from any numeric type)
  • String (stored as a Unicode value but any text or datetime field as input)
  • List (a resizable array of values allowing addition, removal and random access)

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:

  • VarPrint(): Lists all current variable names and values to a string table.
  • VarReset(): Clears the internal caches of all variables. Can take a Key parameter to delete just that variable.
  • VarNum(Key[, Value]): Retrieves (if no Value argument passed) or stores a number value in the Key variable.
  • VarNumExists(Key): Returns true if a number variable exists, false otherwise.
  • VarText(Key[, Value]): Retrieves (if no Value argument passed) or stores a string value in the Key variable.
  • VarTextExists(Key): Returns true if a string variable exists, false otherwise.

Lists

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.

  • VarListExists(Key): Returns true if a list variable exists, false otherwise.
  • VarListLength(Key): Returns the current length of a list if it exists, or NULL if the list does not exist.
  • VarListAdd(Key, Value): Adds an item to a list variable (creating a new list if needed) at the end of the list.
  • VarListInsert(Key, Index, Value): Inserts an item into a list (creating a new list if needed) at specified index. If Index is negative accesses from the end of the array backwards.
  • VarListSet(Key, Index, Value): Sets an item in an existing list at specified index. If Index is negative accesses from the end of the array backwards.
  • VarListRemove(Key, Index): Removes an item from a list variable at specified index. If Index is negative accesses from the end of the array backwards.
  • VarListGet(Key, Index): Gets a value from a list variable at the specified index. If Index is negative accesses from the end of the array backwards.

An Overview of the Code Behind

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.

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++. 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 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

Unit Testing

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

Documentation and Test Coverage

The Coverage Workflow

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:

  • The name is in upper case
  • The text inserted matches the name of the function
  • If an XML function, fixed number of parameters and that all are used
  • If a C++ function, check entry point matches name
  • A test workflow for the function exists
  • An entry exists in the function list
  • A documentation entry has been created in the wiki

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.

Packaging

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).

Wrapping Up

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!

Add a custom footer

Home

Developing Functions

Clone this wiki locally