Alteryx Formula Add Ons

Set of XML and C++ Based Formula Add Ons for Alteryx.

Suggestions of extra useful functions welcome!!

Installation

There is an install.bat script which should promote via UAC, and install the necessary files into the Formula Add Ins directory.

You can uninstall the functions using uninstall.bat script, which again should promote via UAC, and remove the necessary files from the directory.

Manual Installation

If the installer script fails, you can manually install it to %AlteryxBinDirectory%\RuntimeData\FormulaAddIn. You will need to copy all the XML and DLL files.

Current functions

Those functions which require the AlteryxAbacus.dll are labelled with C++

MiscUtils

A few additional general methods for data preparation.

• Version: Gets the major and minor version of the Alteryx Engine as a number (C++)
• IfNull: If first value is null return second
• Coalesce: Given list of values return first non null value (C++)
• ReportError: Raise an error from a formula if a condition is met, otherwise return a specified value (C++)
• LogToFile: Write a message to a log file (C++)
• RangeJoin: Finds the first value in the RangeCSV parameter which is greater than or equal to the Value argument. (C++)

Variables ...

These are very experimental so please use with caution.

• VarPrint: Lists all current variable names and values to a string table (C++)
• VarReset: Clears the internal caches of all variables. Can take a Key parameter to delete just that variable (C++)
• VarNum: Retrieves (if no Value argument passed) or stores a number value in the Key variable (C++)
• VarNumAdd: Stores a number value in the Key variable after adding it to the currently stored number value (C++)
• VarNumExists: Returns true if a number variable exists, false otherwise (C++)
• VarNumRead: Retrieves a number value in the Key variable (C++)
• VarNumWrite: Stores a number value in the Key variable (C++)
• VarText: Retrieves (if no Value argument passed) or stores a string value in the Key variable (C++)
• VarTextAdd: Stores a string value in the Key variable after appending/prepending it to the currently stored number value (C++)
• VarTextExists: Returns true if a string variable exists, false otherwise (C++)
• VarTextRead: Retrieves a string value in the Key variable (C++)
• VarTextWrite: Stores a string value in the Key variable (C++)
• VarListExists: Returns true if a list variable exists, false otherwise (C++)
• VarListLength: Returns the current length of a list if it exists, or NULL if the list does not exist (C++)
• VarListAdd: Adds an item to a list variable (creating a new list if needed) at the end of the list (C++)
• VarListInsert: 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 (C++)
• VarListSet: Sets an item in an existing list at specified index. If Index is negative accesses from the end of the array backwards (C++)
• VarListRemove: Removes an item from a list variable at specified index. If Index is negative accesses from the end of the array backwards (C++)
• VarListGet: Gets a value from a list variable at the specified index. If Index is negative accesses from the end of the array backwards (C++)

MathUtils

Math based functions that I havent got a better home for!

• Int: Rounds a number to nearest integer either equal to the number or closer to 0.
• Modulo: General Double Based Modulo function
• Quotient: Returns the integer part of a division
• Sign: Determines the sign of a number (-1 if less than 0, 1 if greater than, 0 otherwise)
• HexBinX: Given an X,Y point and optional radius, get X co-ordinate of hexagonal bin's centre (C++)
• HexBinY: Given an X,Y point and optional radius get Y co-ordinate of hexagonal bin's centre (C++)
• Rand_Triangular: Given a uniform random number transform into a triangular distributed random
• Avg: Average of a list of values ignoring NULL (C++)
• Count: Count of a list of values ignoring NULL (C++)
• Sum: Sum of a list of values ignoring NULL (C++)
• Deg: Convert radians to degrees
• Rad: Convert degrees to radians
• NormDist: Compute PDF or CDF on Normal distribution (C++)
• NormInv: Compute inverse CDF on Normal distribution (C++)
• LogNormDist: Compute PDF or CDF on Log Normal distribution (C++)
• LogNormInv: Compute inverse CDF on Log Normal distribution (C++)
• TDist: Compute two tailed Student T distribution (C++)
• TInv: Compute inverse two tailed Student T distribution (C++)
• ChiDist: Compute CDF on Chi Squared distribution (C++)
• ChiInv: Compute inverse CDF on Chi Squared distribution (C++)
• Phi: Returns the golden ratio constant.
• RandTriangular: Produces a random number from a triangular distribution
• Int64Add: Sums a set of integers (stored as strings) using Int64 types (C++)
• Int64Mult: Products a set of integers (stored as strings) using Int64 types (C++)
• Int64Div: Divides two integers (stored as strings) using Int64 types (C++)
• Int64Mod: Computes the remainder of dividing two integers (stored as strings) using Int64 types (C++)
• GammaDist: Compute PDF or CDF on Gamma distribution (C++)
• GammaInv: Compute inverse CDF on Gamma distribution (C++)
• PoissonDist: Compute PDF or CDF on Poisson distribution (C++)
• PoissonInv: Compute inverse CDF on Poisson distribution (C++)
• FDist: Compute right tailed F distribution (C++)
• FInv: Compute inverse right tailed F distribution (C++)

DateUtils

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 (C++)
• MakeTime: Create a new time from Hour, Minute, Second (C++)
• MakeDateTime: Create a new DateTime from Year, Month, Day, Hour, Minute, Second (C++)
• ToDate: Truncate a DateTime to a Date
• ToDateTime: Appends midnight to a Date to create a DateTime
• ToTime: Get the time from a DateTime, Date (defaults to 00:00:00) or a Time.

Date Time Parse Functions

• DateFromMDY: Parse a string in Month Day Year format to a Date (copes without leading 0s and different separators)
• DateFromDMY: Parse a string in Day Month Year format to a Date (copes without leading 0s and different separators)

Date Part Functions (returns numbers)

• Day: Get the day of the month for a date or datetime [1-31]
• Month: Gets the month number for a date or datetime [1-12]
• Year: Gets the four digit year
• Century: Gets the century for a date
• WeekDay: Gets the day of the week [Sunday (0) through to Saturday (6)]
• WeekNum: Gets the week number of a Datetime, with Sunday as start of week and January 1st in Week 1 [1-53]
• Quarter: Gets the quarter of the date [1-4]
• OrdinalDay: Gets the day of the year [1-366]
• DatePart: Replicates the SQL DatePart function, getting a specified part of the datetime input

Time Part Functions (returns numbers)

• Hour: Get the hour part of a DateTime or Time [0-23]
• Minute: Gets the minute part of a DateTime or Time [0-59]
• Second: Gets the second part of a DateTime or Time [0-59]

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
• Workday: Add or remove a specified number of weekdays to a date (similar to Excel's Workday function but no support for holidays)
• IsLeapYear: Is a year a leap year (takes a year not a date as an input use IsLearYear(Year([Date]))

StringUtils

A few extension methods for working with strings.

• FindStringLast: Gets the position from the left of the last instance of a term in a string
• 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 (C++)
• ToRoman: Given a number between 0 and 5000, convert to Roman numerals (C++)
• FromRoman: Given Roman numeral convert to a number (C++)

Data Generation

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

Retired Functions

• StartsWith: Does a string start with another
• EndsWith: Does a string end with another
• Contains: Is a string in another

Testing

Assuming you have the CReW Runner macro, there is a workflow RunUnitTest.yxmd which will run all the test workflows checking the formula add ins.