Functions

• Action Tool
• Charting Tool
• Condition Tool
• Dynamic Rename Tool
• Error Message Tool
• Filter Tool
• Formula Tool
• Generate Rows Tool
• Message Tool
• Multi-Field Formula Tool
• Multi-Row Formula Tool
• Report Map Tool
• Table Tool
• Test Tool

A conditional function performs an action or calculation based on a test of data using an IF statement. Use a conditional function to provide a TRUE or FALSE result to highlight or filter out data based on specific criteria. Conditional functions can be used with any data type.

Consider each conditional function before writing your test. Some conditional functions are better suited to specific use cases.

IF c THEN t ELSE f ENDIF: IF condition THEN true ELSE false ENDIF

Example

IF [Class]==1 THEN "Gold" ELSE "Other" ENDIF

Class	Result
1	Gold
2	Other
1	Gold
3	Other

IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF: IF condition THEN true ELSEIF condition2 THEN true2 ELSE false ENDIF

Multiple ELSEIF statements can be included.

Example

IF [Class]==1 THEN "Gold" ELSEIF [Class]==2 THEN "Silver" ELSE "Other" ENDIF

Class	Result
1	Gold
2	Silver
1	Gold
3	Other

IIF(bool,x,y): If ([bool] is true) return (x), else return (y)

Example

IIF([CUSTOMER], "Send flyer", "Send documentation")

If the [CUSTOMER] field value is TRUE, then it returns Send flyer.

If the [CUSTOMER] field value is FALSE, then it returns Send documentation.

Switch(Value,Default,Case1,Result1,...,CaseN,ResultN): Compares a value against a list of cases and returns the corresponding result.

Example

Switch([Class], Null(), "Gold",1,"Silver",2,"Bronze", 3,"Tin", 4, "Aluminum",5)

Result	Class
5	Aluminum
2	Silver
1	Gold
3	Bronze
4	Tin
2	Silver
1	Gold
Null	Other

A conversion function converts one data type or format to another data type or format. Use a conversion function to convert numbers to strings or strings to numbers. Conversion functions can be used with String and Number data types.

BinToInts(s): Converts the binary string s to an integer. (Limited to 53 bits.)

Example

BinToInts(101010101) results in 341

CharFromInt(x): Returns the Unicode® character that matches the input number.

Example

CharFromInt(66) returns B (U+0042 ‘Latin Capital Letter B’)

CharFromInt(169) returns © (U+00A9 ‘copyright sign’)

CharFromInt(1071) returns Я (U+042F ‘Cyrillic capital letter YA’)

CharFromInt(127944) returns (U+1F3C8 ‘American football’)

CharFromInt(0) returns [null] (U+000 ‘Null’) because any integer that cannot be used to represent a character may give a null result.

CharFromInt(55300) returns [null] because any integer that does not currently represent a character will not be rendered by a normal font.

CharToInt(S): Returns the number that matches the input Unicode® character.

Example

CharFromInt(B) returns 66 (U+0042 ‘Latin Capital Letter B’)

CharFromInt(©) returns 169 (U+00A9 ‘copyright sign’)

CharFromInt(Я) returns 1071 (U+042F ‘Cyrillic capital letter YA’)

CharFromInt() returns 127944 (U+1F3C8 ‘American football’)

ConvertFromCodepage(s, codePage): Translates text from a code page to Unicode. See Code Pages.

ConvertToCodepage(s, codePage): Translates text from Unicode® encoding to a specific code page. See Code Pages.

HexToNumber(x): Converts a HEX string to a number. (Limited to 53 bits.)

Example

HexToNumber(dd) converts to the number 221

IntToBin(x): Converts x to a binary string

IntToHex(x): Converts x to a hexadecimal string

ToNumber(x, bIgnoreErrors, keepNulls, decimalSeparator): Converts a string parameter to a number. ToNumber accepts strings that can be interpreted as scientific notation double precision. By default, the period is used as the decimal separator.

Optional parameters

bIgnoreErrors: 0 or false (default) reports conversion error messages; 1 or true ignores conversion errors.

keepNulls: 0 or false (default) converts non-numeric values (including null) to zero; 1 or true converts non-numeric values to null.

decimalSeparator: "." (default) specifies the period as decimal separator; "," specifies the comma as decimal separator.

Example

ToNumber("878") returns the string 878 as a number.

ToNumber("4.256411411E9") returns the string 4256411411 as a number.

ToNumber("Number", "false") returns 0 with conversion error: TONUMBER: Number lost information in conversion.

ToNumber("Number", 0, 0) returns 0 with conversion error: TONUMBER: Number lost information in conversion.

ToNumber("Number", 1, 0) returns 0 with no conversion error.

ToNumber("Number", 1, 1) returns [Null] and no conversion error.

ToNumber("123456,789", 1, 1, ",") returns 123456.789 as a number.

ToNumber("123.456,789", 1, 1, ",") returns 123 as a number. (This is because conversion stops at the thousands separator, which is not valid for input with the ToNumber function.)

ToString(x, numDec, addThousandsSeparator, decimalSeparator): Converts a numeric parameter to a string using numDec decimal places. By default, the period is used as the decimal separator.

Optional parameters

addThousandsSeparator: 0 (default) formats the numeric string without a thousands separator; 1 formats with a thousands separator. By default, the thousands separator is a comma unless "," is specified for decimalSeparator, in which case it is a period.

decimalSeparator: "." (default) specifies the period as decimal separator; "," specifies the comma as decimal separator.

Example

ToString(10, 0) returns 10 as a string.

ToString(10.4, 2) returns 10.40 as a string.

ToString(100.4, 2) returns 100.40 as a string.

ToString(1000.4, 2, 1) returns 1,000.40 as a string.

ToString(123456.789, 3, 1, ",") returns 123.456,789 as a string.

ToString(123456.789, 3, 0, ",") returns 123456,789 as a string.

A DateTime function performs an action or calculation on a date and time value. Use a DateTime function to add or subtract intervals, find the current date, find the first or last day of the month, extract a component of a DateTime value, or convert a value to a different format.

Alteryx uses the ISO format yyyy-mm-dd HH:MM:SS to represent dates and times. If a DateTime value is not in this format, Alteryx reads it as a string. To convert a column for use and manipulation in the DateTime format, use the DateTimeParse function in the expression editor or the DateTime Tool.

Some DateTime functions require you to set the format for the date. Format strings are comprised of specifiers and separators.

Specifiers

Specifiers always begin with a percent sign (%), followed by a case-sensitive letter. The data must include at least a two digit year.

Specifier	Output from DateTimeFormat	Supported Input with DateTimeParse
%a	Abbreviated weekday name ("Mon")	Any valid abbreviation of a day of the week ("mon", "Tues.", "Thur"), giving an error only if the text given is not a day of the week. Note that Alteryx does not check that the specified day name is valid for a particular date.
%A	Full weekday name ("Monday")	Day name or any valid abbreviation of a day of the week ("mon", "Tues.", "Thur"), giving an error only if the text given is not a day of the week. Note that Alteryx does not check that the specified day name is valid for a particular date.
%b	Abbreviated month name ("Sep")	Any valid abbreviation of a month name ("Sep", "SEPT."), giving an error only if the text given is not a name of a month.
%B	Full month name ("September")	Month name or any valid abbreviation of a month name ("Sep", "SEPT."), giving an error only if the text given is not a name of a month.
%c	The date and time for the computer’s locale	Not supported
%C	The century number ("20")	Not supported
%d	Day of the month ("01")	One or two digits, ignoring spaces ("1" or "01")
%D	Equivalent to %m/%d/%y	Not supported
%e	Day of the month, leading 0 replaced by a space (" 1")	One or two digits, ignoring spaces ("1" or "01")
%h	Same as %b ("Sep")	Any valid abbreviation of a month name ("Sep", "SEPT."), giving an error only if the text given is not a name of a month.
%H	Hour in 24 hour clock, 00 to 23	Up to two digits for hour, 0 to 23. Not compatible with %p or %P.
%I (capital "eye")	Hour in 12 hour clock, 01 to 12	Up to two digits for hour, 1 to 12. Must follow with %p or %P.
%j	The day of the year, from 001 to 365 (or 366 in leap years)	3-digit day of the year, from 001 to 365 (or 366 in leap years)
%k	24 hours, leading zero is space, " 0" to "23"	Up to two digits for hour
%l (lowercase "ell")	12 hours, leading zero is space, " 1" to "12"	Not supported
%M	Minutes, 00 to 59	Up to two digits for minutes
%m	Month number, 01 to 12	One or two digit month number, 1 or 01 to 12
%p	"AM" or "PM"	Case blind ("aM" or "Pm"). Must follow %I (capital "eye", hour in 12-hour format)
%P	"am" or "pm"	Case blind ("aM" or "Pm"). Must follow %I (capital "eye", hour in 12-hour format)
%S	Seconds, 00 to 59	Up to two digits for seconds
%T	Time in twenty-four hour notation. Equivalent to %H:%M:%S	Not supported
%u	Day of week as a decimal, 1 to 7, with Monday as 1	Not supported
%U	This returns the week number, as 00 – 53, with the beginning of weeks as Sunday.	Not supported
%w	Day of week as a number, 0 to 6, with Sunday as 0	Not supported
%W	This returns the week number, as 00 – 53, with the beginning of weeks as Monday.	Not supported
%x	The date for the computer’s locale	Not supported
%X	The 12-hour clock time, including AM or PM (“11:51:02 AM”)	Hours:Minutes:Seconds [AM / PM]
%y	Last two digits of the year ("16")	Up to four digits are read, stopping at a separator or the end of the string, and mapped to a range of the current year minus 66 to current year plus 33. (For example, in 2016, that's 1950 to 2049.) Limitation with six-digit dates Because up to four digits are read for the year, formats that are intended to have only two digits without a separator, such as a six-digit date stamp (for example, %y%m%d for data resembling 170522 for May 22, 2017), are still read as four digits. To work around this limitation, you can: Use four digits for the year (for example, 2017 instead of 17), depending on your range of dates. Use the RegEx Tool to insert a space after the first two digits in the string.
%Y	All four digits of the year ("2016")	Two or four digits are read. Two digits are mapped to a range of the current year minus 66 to current year plus 33. (For example, in 2016, that's 1950 to 2049.)
%z	Offset from UTC time (“-600”)	Not supported
%Z	Full timezone name (“Mountain Daylight Time”)	Not supported

Separators

Separators are inserted between DateTime specifiers to form a format string.

Separator	Output from DateTimeFormat	Supported Input with DateTimeParse*
/	/	/ or -
-	-	/ or -
space	A space	Any sequence of white space characters
%n	A newline	Not supported
%t	A tab	Not supported
other	Other characters, such as comma, period, and colon	Other characters, such as comma, period, and colon

* DateTimeParse accepts forward slashes ( / ) and hyphens ( - ) interchangeably. However, commas, colons, and all other separators must match the incoming data exactly.

DateTimeAdd(dt,i,u): Adds a specific interval to a DateTime value.

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

i: Positive or negative integer of time to add or subtract.

u: DateTime unit, specified between quotes: years, months, days, hours, minutes, or seconds.

Example

DateTimeAdd(DateTimeToday(), -1, "days") returns yesterday’s date.

DateTimeAdd(DateTimeFirstOfMonth(), 1, "months")returns the first of next month.

DateTimeAdd("2016-01-30", 1, "month") returns 2016-02-29 (because February does not have a 30th, but its last day that year is the 29th)

DateTimeAdd("2016-03-30", -1, "month")returns 2016-02-29 (because February does not have a 30th, but its last day that year is the 29th)

Read more

• Any fraction in the duration is truncated. For example, you cannot add "1.5 hours". Instead, add "90 minutes".
• Adding a unit does not change the value of smaller units. For example, adding hours does not change the value of minutes or seconds. Adding months does not change the day or time, unless the resulting month would not have such a day. In that case, it goes to the last day of that month.

DateTimeDay(dt): Return the numerical value for the day of the month in a DateTime value.

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

Example

DateTimeDay("2017-03-24 11:43:23") returns 24.

DateTimeDiff(dt1,dt2,u): Subtract the second argument from the first and return it as an integer difference. The duration is returned as a number, not a string, in the specified time units.

Example

DateTimeDiff("2016-02-15 00:00:00", "2016-01-15 00:00:01", "Months") returns 1 (because the start and end are the same day of the month)

DateTimeDiff("2012-02-29","2011-03-01","years") returns 0 (even though 2012-02-29 is 365 days after 2011-03-01, February 29 is before March 1st, so “one year” has not yet been completed)

DateTimeDiff("2016-02-14", "2016-01-15", "Months") returns 0 (because the day in February is less than the day in January)

DateTimeDiff("2016-02-14 23:59:59", "2016-01-15 00:00:00", "Months") returns 0 (even though it is only one second short of reaching the required day)

DateTimeDiff('2017-02-28', '2016-02-29', 'Months') returns 11 (even though the 28th is the last day of February in 2017, the 28 is less than 29)

Read more

• For Month and Year differences, a month is only counted when the end day reaches the start day, ignoring the time of day.
• For precision of Day, Hour, Minute, and Second, the result is calculated precisely, then fractional parts are truncated, not rounded. Therefore:

DateTimeDiff(‘2016-01-01 00:59:59’, ‘2016-01-01 00:00:00’, ‘Hours’) is zero.

DateTimeDiff(‘2016-01-01 23:59:59’, ‘2016-01-01 00:00:00’, ‘Days’) is zero.

• Precision names can be shortened to their first three characters (like ‘sec’ and ‘min’); case is insensitive.
• Be careful when storing time differences in seconds. An Int32 can only hold a difference of 68 years in seconds, or 4082 years in minutes. You can use a Double or an Int64 to hold intervals between all supported dates.

DateTimeFirstOfMonth(): Returns the first day of the month, at midnight.

DateTimeFormat(dt,f): Convert DateTime data from ISO format to a format for use by another application. Output to a string data type.

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

f: The format to which to convert the data, expressed in a format string.

Example

DateTimeFormat([DateTime_Out],"%d-%m-%Y") returns 22-04-2008 for the date April 22, 2008 (ISO format: 2008-04-22)

DateTimeHour(dt): Return the hour portion of the time in a DateTime value.

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

Example

DateTimeHour("2017-03-24 11:43:23") returns 11.

DateTimeHour("2017-03-24") returns 0, as midnight is the assumed hour when no time is specified with a date.

DateTimeLastOfMonth(): Returns the last day of the current month, with the clock set to one second before the end of the day (23:59:59).

Designer uses the date and time when the formula is first parsed. In a batch process, this time is used with each new set of data. This allows for consistency if the process takes a long time.

DateTimeMinutes(dt): Return the minutes portion of the time in a DateTime value.

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

Example

DateTimeMinutes("2017-03-24 11:43:23") returns 43.

DateTimeMonth(dt): Return the numerical value for the month in a DateTime value.

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

Example

DateTimeMonth("2017-03-24 11:43:23") returns 3.

DateTimeMonth("11:43:23") returns [Null], because the incoming data is not valid.

DateTimeNow(): Returns the current system date and time.

DateTimeParse(dt,f): Converts a date string with the specified format to the standard ISO format yyyy-mm-dd HH:MM:SS.

Parameters

dt: DateTime string data, expressed as a selected field or a DateTime string between quotes. The incoming data must be a String data type, and can be in any format of DateTime as long as this format agrees with the format you specify for the f parameter.

f: The format of the data you are converting, expressed in a format string between quotes.

Example

DateTimeParse("2016/28-03","%Y/%d-%m") returns 2016-03-28.

DateTimeSeconds(dt): Return the seconds portion of the time in a DateTime value.

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

Example

DateTimeSeconds("2017-03-24 11:43:23") returns 23.

DateTimeStart(): Returns the date and time when the current workflow started running.

DateTimeToday(): Returns today’s date.

ToDateTime(DateTimeToday())

DateTimeToLocal(dt): Converts a UTC DateTime to the local system time zone.

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

Example

DateTimeToLocal('2014-08-01 20:01:25') returns the local system time zone (Mountain Time) as 2014-08-01 14:01:25

DateTimeToUTC(dt): Converts a DateTime (in local system time zone) to UTC.

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

Example

DateTimeToUTC(DateTimeNow()) returns the Coordinated Universal Time at workflow runtime: 2014-08-01 20:01:25 (where Local Mountain time was 2014-08-01 14:01:25)

DateTimeTrim(dt,t): Remove unwanted portions of a DateTime and return the modified DateTime .

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

t: Trim type. Options include:

• firstofmonth:  trim to the beginning of the month (this does the same as month)
• lastofmonth: extend to one second before the end of the last day of the month
• year: trim to midnight on January 1st.
• month: trim to midnight at the first day of the month
• day: trim to the day (i.e., midnight). This converts a DateTime to a day with a time of zero (not a date).
• hour:  trim to the hour
• minute:  trim to the minute.

Trimming a DateTime does not round the returned value. For example, the time 15:59:59 trimmed to the hour becomes 15:00:00, not 16:00:00.

Example

DateTimeTrim("2016-12-07 16:03:00","year") returns 2016-01-01 00:00:00.

DateTimeYear(dt): Return the numerical value for the year in a DateTime value.

Parameters

dt: DateTime data, expressed as a selected column or a specified DateTime value between quotes.

Example

DateTimeYear("2017-03-24 11:43:23") returns 2017.

ToDate(x): Converts a string, number, or DateTime to a Date.

ToDateTime(x): Converts a string, number, or Date to a DateTime.

A file function builds file paths, checks to see if a file exists, or extracts a part of a file path. File functions can only be used with String data types.

FileAddPaths(Path1, Path2): Adds two file path parts, making sure there is exactly one \ (backslash) between the two paths.

Example

FileAddPaths([C:\Temp], [Data\file.csv]) returns "C:\Temp\Data\file.csv"

FileExists(Path): Returns true if the file exists, false if it does not.

Example

FileExists(C:\Temp) returns "True"

FileExists(C:\Temp\Data\file.csv) returns "True"

FileExists(C:\Temp\Data\NoFile.csv) returns "False"

FileGetDir(Path): Returns the directory portion of the path.

Example

FileGetDir(C:\Temp\Data\file.csv) returns C:\Temp\Data

FileGetDir(C:\Temp\Data) returns C:\Temp

FileGetExt(Path): Returns the extension of the path, including the . (period).

Example

FileGetExt(C:\Temp\Data\file.csv) returns ".csv"

FileGetExt(C:\Temp\Data) returns " "

FileGetFileName(Path): Returns the name portion of the path, without the extension.

Example

FileGetName(C:\Temp\Data\file.csv) returns "file"

FileGetName(C:\Temp\Data) returns "Data"

A finance function applies financial algorithms or mathematical calculations.

FinanceCAGR(BeginningValue, EndingValue, NumYears): Compound Annual Growth Rate: The geometric mean growth rate on an annualized basis.

FinanceEffectiveRate(NominalRate, PaymentsPerYear): Effective Annual Interest Rate: The interest rate on a loan or financial product restated from the nominal interest rate as an interest rate with annual compound interest payable in arrears

FinanceFV(Rate, NumPayments, PaymentAmount, PresentValue, PayAtPeriodBegin): Future Value of an Investment: The value of an asset at a specified time in the future assuming a certain interest rate or rate of return.

FinanceFVSchedule(Principle, Year1Rate, Year2Rate): Future Value Schedule: The future value of an initial principal after applying a series of interest rates to an investment.

FinanceIRR(Value1, Value2): Internal Rate of Return: The interest rate at which the costs of the investment lead to the benefits of the investment. This means that all gains from the investment are inherent to the time value of money and that the investment has a zero net present value at this interest rate.

FinanceMIRR(FinanceRate, ReinvestRate, Value1, Value2): Modified Internal Rate of Return: A modification of the internal rate of return that aims to resolve some problems with the IRR. The MIRR is a financial measure of an investment's attractiveness.

FinanceMXIRR(FinanceRate, ReinvestRate, Value1, Date1, Value2, Date2): Modified Internal Rate of Return of an investment with dates

FinanceNominalRate(EffectiveRate, PaymentsPerYear): Nominal Annual Interest Rate: An interest rate is called nominal if the frequency of compounding (such as a month) is not identical to the basic time unit (normally a year).

FinanceNPER(Rate, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin): Number of periods for an investment or loan

FinanceNPV(Rate, Value1, Value2): Net Present Value of an investment: Measures the excess or shortfall of cash flows, in present value terms, once financing charges are met.

FinancePMT(Rate, NumPayments, PresentValue, FutureValue, PayAtPeriodBegin): Calculates payments on a loan

FinancePV(Rate, NumPayments, PaymentAmount, FutureValue, PayAtPeriodBegin): Present Value of an Investment: The value on a given date of a future payment or series of future payments, discounted to reflect the time value of money and other factors such as investment risk.

FinanceRate(NumPayments, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin): Returns the interest rate (per period)

FinanceXIRR(Value1, Date1, Value2, Date2): Internal Rate of Return of an investment with dates

FinanceXNPV(Rate, Value1, Date1, Value2, Date2): Net Present Value of an investment with dates

A math function performs mathematical calculations. Math functions can only be used with Number data types.

ABS(x): Absolute value of (x). It is the distance between 0 and a number. The value is always positive.

Example

ABS(32) returns 32

ABS(-32) returns 32

ACOS(x): Arc - Cosine of (x)

Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is (x). The returned angle is given in radians in the range 0 (zero) to pi. The number (x) must be between -1 and 1.

Example

ACOS(0.5) returns 1.047198

ASIN(x): Arc - Sine of (x)

Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is (x). The returned angle is given in radians in the range -pi/2 to pi/2.

Example

ASIN(0.5) returns 0.523599

ATAN(x): Arc - Tangent of (x)

Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is (x). The returned angle is given in radians in the range -pi/2 to pi/2.

Example

ATAN(0.8) returns 0.674741

ATAN2(y, x): Arc - Tangent of (y/x)

Returns the arctangent of y and x.

Example

ATAN2(4, -3) returns 2.2143

Average(n1,...): Average of a list of numbers.

Example

Average(20,30,55) returns 35 since [(20+30+55)/3]= 35

CEIL(x): Returns smallest integer greater than or equal to (x). Works like the RoundUp function in Excel.

Example

CEIL(1.1) returns 2

CEIL (6.54) returns 7

CEIL(-30.42) returns -30

COS(x): Cosine of (x)

COSH(x) : Hyperbolic Cosine of (x)

DISTANCE(from_Lat,from_Lon, to_Lat, to_Lon): Returns distance from (lat1,Lon1) to (lat2,lon2)

Example

DISTANCE(42,-90, 43, -80) returns a value of 513.473706 miles

EXP(x): Returns e^x

FLOOR(x): Returns largest integer less than or equal to (x)

Example

FLOOR(1.1) returns 1

FLOOR(6.54) returns 6

FLOOR(-30.42) returns -31

LOG(x): Natural logarithm of (x)

Example

LOG(1) returns 0

LOG(14) returns 2.639057

LOG10(x): Base 10 logarithm of (x)

Example

LOG10(1) returns 0

LOG10(14) returns 1.146128

Median(...): Calculates the median out of one or more (possibly unsorted) values.

Example

Median (5, 4, 3, 7, 6) returns 5

Mod(n,d): Modulo of n divided by d (integer operation)

The Modulo operation finds the remainder of one integer (n) divided by a divisor integer (d). If (n) is positive, the result is positive. If (n) is negative, the result is negative.

If decimal values are used, the decimal is truncated. This can result in a "Divided by 0" error if the divisor is a decimal less than 1.

Example

MOD(6,4) returns 2

MOD(6,-4) returns 2

MOD(-6,4) returns -2

MOD(-6,-4) returns -2

MOD(6,.4) returns [Null]

PI(): Returns the value of the constant PI to 15 digits of accuracy

Example

PI() returns   3.141593

POW(x, e): Returns (x) raised to the (e) power

Example

POW(2, 3) is equivalent to 2 ³ and returns 8

RAND(): Returns a random number between 0 and 1

Example

RAND() returns a random number like 0.256

RandInt(n): Returns a random integer between 0 and the specified parameter

Example

RandInt(10) returns 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 or 10

RandInt(5000) returns a random whole number between 0 and 5000, such as 741

Round(x, mult): Returns (x) rounded to nearest multiple of (mult)

Example

Round(55.34, 2) returns a value of 56

Round(39.88, 10) returns a value of 40

Round(41.1, 10) returns a value of 40

Round(1.25, 0.1) returns a value of 1.3

Round(1.227, 0.01) returns a value of 1.23

SIN(x): Sine of (x)

SINH(x): Hyperbolic Sine of (x)

SmartRound(x): Returns (x) rounded to nearest multiple of a value determined dynamically based on the size of (x)

SQRT(x): Square root of (x)

Example

SQRT(100) returns a value of 10

TAN(x): Tangent of (x)

TANH(x): Hyperbolic Tangent of (x)

A bitwise function operates on one or more bit patterns or binary numerals at the level of their individual bits. Use a bitwise function to manipulate values for comparisons and calculations. Bitwise functions can only be used with Number data types.

BinaryAnd(n,m): Returns a binary of n and m. The result is 1 if both n and m are 1, and 0 otherwise. If 0 is equated with false, and 1 with true the BinaryAnd operation works like a logical And.

Example

BinaryAnd(1,1) returns 1

BinaryAnd(1,0) returns 0

BinaryNot(n): Returns a Binary Not of n

Example

BinaryNot(6) returns -7

BinaryNot(2) returns -3

BinaryOr(n,m): Returns a Binary Or of n and m

Example

BinaryOr(6,6) returns 6

BinaryOr(6,2) returns 6

BinaryXOr(n,m): Returns a Binary XOr of n and m

Example

BinaryXOr(6,6) returns 0

BinaryXOr(6,2) returns 4

ShiftLeft(n,b): Left shifts n (as integer) by b bits

ShiftRight(n,b): Right shifts n (as integer) by b bits

A minimum or maximum function finds the smallest and largest value of a set of values. Min/Max functions can only be used with Number data types.

Bound(x, min, max): If (x < min) return min; else if (x > max) return max; else return x

Example

Bound(6,1,5) returns 5

Bound(3,1,5) returns 3

Max(v0, v1,..., vn): Returns the maximum value from the list.

Example

Max(15,180,7,13,45,2,13) returns 180

MaxIDX(v0, v1,..., vn): Returns the 0 based index of the maximum value from the list.

Example

MaxIDX(15,180,7,13,45,2,13) returns 1

Min(v0, v1,..., vn): Returns the minimum value from the list.

Example

Min(15,180,7,13,45,2,13) returns 2

MinIDX(v0, v1,..., vn): Returns the 0 based index of the minimum value from the list.

Example

MinIDX(15,180,7,13,45,2,13) returns 5

An operator is a character that represents an action. Use an arithmetic operator to perform mathematical calculations or a Boolean operator to work with true/false values. Operators can be used with all data types.

/*Comment*/: Block Comment: Allows you to add comments to an expression editor (within the expression line) without interfering with the expression.

//Comment: Single Line Comment: Allows you to add comments to an expression editor without interfering with the expression.

Addition +: Addition

Boolean AND &&: Boolean AND: Used to combine two Boolean values. The result is also a Boolean value. The result is true if both of the combined values are true, and the result is false if either of the combined values is false.

Boolean AND - Keyword: Boolean AND: Used to combine two Boolean values. The result is also a Boolean value. The result is true if both of the combined values are true, and the result is false if either of the combined values is false.

Boolean NOT !: Boolean NOT: Accepts one input. If that input is TRUE, it returns FALSE. If that input is FALSE, it returns TRUE.

Boolean NOT - Keyword: Boolean NOT: Accepts one input. If that input is TRUE, it returns FALSE. If that input is FALSE, it returns TRUE.

Boolean OR - Keyword: Boolean OR:  If either (or both) of the two values it checks are TRUE, then it returns TRUE.

Boolean OR ||: Boolean OR:  If either (or both) of the two values it checks are TRUE, then it returns TRUE.

Close Parenthesis ): Close Parenthesis

Division /: Division

Equal To =: Equal To

Greater Than >: Greater Than

Greater Than Or Equal >=: Greater Than Or Equal

Less Than <: Less Than

Less Than Or Equal <=: Less Than Or Equal

Multiplication *: Multiplication

Not Equal To !=: Not Equal To

Open Parenthesis (: Open Parenthesis

Subtraction -: Subtraction

value IN (...) - Keyword: Test Value in List: Determines if a given value matches any value in a subquery or a list.

value NOT IN (...) - Keyword: Test Value Not in List: Determines if a given value matches any value not in a subquery or a list.

A spatial function builds spatial objects, analyze spatial data, and returns metrics from spatial fields. A spatial function can only be used with Spatial data types.

Spatial Functions are consistent with the Open GIS Consortium, Inc. For more information see http://portal.opengeospatial.org/files/?artifact_id=829.

Spatial objects in formulas can use operators: plus +; minus -; equals ==; not equal !=

ST_Area(object, units): Returns the area in the specified units of the spatial object. (numeric data value)

ST_Boundary(object): Returns the boundary of the spatial object. (polyline spatial object indicating the boundary of the input polygon)

ST_BoundingRectangle(object, ...): Returns the bounding rectangle of the spatial object. (polygon spatial object)

Example

ST_Centroid(object): Returns the centroid of the spatial object. (point spatial object)

ST_CentroidX(object): Returns the longitude of the centroid of the spatial object (numeric data value)

ST_CentroidY(object): Returns the latitude of the centroid of the spatial object (numeric data value)

ST_Combine(object1, object2,...): Combines the spatial objects. (spatial object)

Example

ST_Contains(object1,object2): Returns True if object1 contains object2. (Boolean value)

ST_ConvexHull(object1,...): Returns the convex hull of the object(s). (spatial object)

Example

ST_CreateLine(point1, point2...): Creates a line by connecting the specified points and lines in a sequence. (spatial object)

ST_CreatePoint(x,y): Returns a spatial object containing the specified longitude and latitude coordinates. (spatial object)

ST_CreatePolygon(obj1, obj2...): Creates a polygon by connecting the specified points and lines in a sequence. (spatial object)

ST_Cut(object1,object2): Returns the result of cutting object1 from object 2. (spatial object)

Example

ST_Dimension(object): Returns the dimension of the spatial object. The spatial dimension is the minimum number of coordinates needed to specify every point that makes up the spatial object. Point objects return a value of 0, Line objects return a value of 1, and Polygon objects return a value of 2. (numeric data value)

ST_Distance(object1, object2, units): Returns the distance in specified units from object1 to object2. Supported units are Miles (Mi), Kilometers (KM), Meters, Feet. (numeric data value)

ST_EndPoint(object): Returns the last point of the spatial object. (point spatial object)

ST_Intersection(object1, object2,...): Returns the intersection of the specified spatial objects. (spatial object)

ST_Intersects(object1, object2...): Returns True if the spatial objects intersect. (Boolean value)

ST_InverseIntersection(object1, object2...): Returns the inverse intersection of the specified spatial objects. (spatial object)

ST_Length(object, units): Returns the linear length of the spatial object. (numeric data value)

ST_MaxX(object): Returns the maximum longitude of the spatial object. (numeric data value)

ST_MaxY(object): Returns the maximum latitude of the spatial object. (numeric data value)

ST_MinX(object): Returns the minimum longitude of the spatial object. (numeric data value)

ST_MinY(object): Returns the minimum latitude of the spatial object. (numeric data value)

ST_NumParts(object): Returns the number of parts in the spatial object. (numeric data value)

ST_NumPoints(object): Returns the number of points in the spatial object. (numeric data value)

ST_ObjectType(object): Returns the spatial object type as a string value. (string data value)

ST_PointN(object, n): Returns the Nth point in the spatial object. (point spatial object)

ST_RandomPoint(object): Returns a random point within the spatial object. (point spatial object)

ST_Relate(object1,object2,relation): Returns True if the objects satisfy the provided DE-9IM relation. (Boolean value)

ST_StartPoint(object): Returns the first point in the spatial object. (spatial object)

ST_Touches(object1, object2): Returns True if object 1 touches object 2. (Boolean value)

ST_TouchesOrIntersects(object1, object2): Returns True if object 1 touches or Intersects object 2. (Boolean value)

ST_Within(object1, object2): Returns True if object 1 is contained by object 2. (Boolean value)

These functions perform a variety of specialized actions and can be used with all data types.

EscapeXMLMetacharacters(String): Replaces XML metacharacters with their escaped versions. There are five characters that will be escaped.

Example

EscapeXMLMetacharacters("2>1&1<2") returns 2&gt;1&amp;1&lt;2

GetVal(index, v0,...vn): Returns the value (v0,..., vn) specified by the 0-based [index].

GetEnvironmentVariable(Name): Returns the environment variable specified in Name. To get a list of environment variables, go to Control Panel > System > Advanced System Settings > Environment Variables. A list of system variables appears. Any value from this can be used.

Example

GetEnvironmentVariable(CommonProgramFiles) returns C:\Program Files\Common Files

GetEnvironmentVariable(OS) returns Windows_NT

Message(messageType, message, returnValue): Use with a conditional statement to output a message to the message log and update column data with a specified value when a condition is met.

The message type and text is shown in the Results window. If logging is enabled, the output log file also resembles this information. See Results Window and Output Log File.

Parameters

messageType: A number that corresponds to the type of message:

• 1 = Messages
• 2 = Warnings
• 3 = Errors
• 5 = Conv Errors (field conversion errors)
• 8 = Files (input)
• 9 = Files (output)

message: The text of the message, expressed as a string between quotes.

returnValue: A value to output to column data. This can be a numeric value (for example, 0), null, or a text string between quotes (for example, "False").

Example

In this example, the Message function is used within a conditional expression to output a field conversion error message and update column data with a value of "False" if given date/time values do not contain valid time data.

If [Hour] = "0" and [Minutes] = "0" then
Message(5, "Invalid time in DateTime", "False")
Else
"True"
Endif

NULL(): Returns a Null value

RangeMedian(...): Calculates the median from a series of aggregated ranges. See Range Median.

ReadRegistryString(Key, ValueName, DefaultValue="): Returns a value from the registry.

Example

ReadRegistryString('HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SRC\Alteryx', 'InstallDir64') returns C:\Program Files\Alteryx\bin

Soundex(string): Returns the Soundex of the String. Soundex creates a code based on the first character in the string plus three characters based on the following:

Example

If the resulting code is only 2 or 3 characters long, Soundex uses zeros to fill out the code to four characters. For example, in the name Lauren, only the L, r, and n are translated (Lrn), so the resulting Soundex code becomes L650.If the resulting code is more than four characters long, all characters after the fourth character are ignored. For example, in the name Patrick, the P, t, r, c, and k can be translated (Ptrck), but the resulting Soundex code will only be four characters: P362.

Soundex_Digits(string): Returns the first four digits or the Soundex if none. If there are digits (numbers) in the String, the first four digits are returned. If there are no digits, the Soundex code is returned.

TOPNIDX(N, v0, v1, ..., vn): Returns the 0 based index of the Nth from the maximum value from the list. With N==0, it is the same as MaxIdx(...)

N is the starting point, v0 is the indexed field (subsequent Variables are optional), and vn is the goal.

Example

TOPNIDX(0, [IndexedField], 5) returns the top five of a stream

UrlEncode(String): Encodes string as a web legal URL

Example

C:\temp\Alteryx URL Encode returns C:/temp/Alteryx%20URL%20Encode

A string function performs operations on text data. Use a string function to cleanse data, convert data to a different format or case, compute metrics about the data, or perform other manipulations. A string function can only be used with String data types.

Contains(String, Target, CaseInsensitive=1): Searches for the occurrence of a particular string within a string. Returns a TRUE or FALSE if the string matches the pattern. It is defaulted to case insensitive.

The CONTAINS function is case insensitive while the FINDSTRING function is case sensitive.

Example

Contains('123ABC', 'ABC') returns TRUE

Contains('123ABC', 'abc') returns TRUE

Contains('123ABC', 'abc', 0) returns FALSE

CountWords(String): Returns the count of words in the specified String. Words are defined by characters separated by a space.

Example

CountWords("Basic Variables Households") returns 3

CountWords("Basic Variables Age:Female (Pop)Age  1") returns 5

DecomposeUnicodeForMatch(String): Removes accents and expands compound characters while converting to narrow. This function takes a Unicode® string and translates it to a lower case, narrow character string. All accents and other decorations will be removed.

This function is useful for matching only. It is not considered a normalized string.

Example

DecomposeUnicodeForMatch("Prénoms français") returns prenoms francais

EndsWith(String, Target, CaseInsensitive=1): Checks if a string ends with a particular string. Returns a TRUE or FALSE if the string matches the pattern. It is defaulted to case insensitive.

Example

EndsWith('123ABC', 'ABC') returns TRUE

EndsWith('123ABC', 'abc') returns TRUE

EndsWith('123ABC', 'abc', 0) returns FALSE

The EndsWith function is case insensitive while the FindString function is case sensitive.

FindString(String,Target): Searches for the occurrence of a particular string (Target) within another string (String) and returns the numeric position of its occurrence in the string.

Returns the 0 based index of the first occurrence of Target in String. Returns -1 if no occurrence.

Example

FindString([Name], "John") returns 0 when the string contains John and returns -1 when the string does not.

IF (FINDSTRING([Name], "John") =0) THEN "John Smith" ELSE "Other" ENDIF returns John Smith when the string contains John and returns Other when the string does not.

GetWord(String, n): Returns the Nth (0-based) word in the String. Words are defined as a collection of characters separated by a space. 0-based index, means the first word is at the 0 position.

Example

GetWord("Basic Variables Households", 0) returns "Basic"

GetWord("Basic Variables Households", 1) returns "Variables"

Left(x, len): Returns the first [len] characters of the string (x). If len is less than 0 or greater than the length of x, x remains unchanged.

Example

Left("92688", 3) returns a value of "926"

Length(x) : Returns the length the string (x).

Example

Length("92688") returns a value of 5

LowerCase(x): Converts a string to lower case

Example

LowerCase("M1P 1G6") returns "m1p 1g6"

MD5_ASCII(String): Calculates the MD5 hash of the string. The string is expected to be only ASCII characters. Unicode® characters are turned into ? before calculating the MD5 hash.

MD5_UNICODE(String): Calculates the MD5 hash of the string.

PadLeft (str, len, char): Pads the string to the left with the specified character to the specified length. If the padding “char” is more than one character long, only the first character will be used.

Example

PadLeft("M", 4, "x") returns "xxxM"

PadRight (str, len, char): Pads the string to the right with the specified character to the specified length. If the padding “char” is more than one character long, only the first character will be used.

Example

PadRight("M", 4, "x") returns "Mxxx"

REGEX_CountMatches(string,pattern,icase): Returns the count of matches within the string to the pattern.

The icase is an optional parameter. When specified, the case must match. By default icase=1 meaning ignore case. If set to 0, the case must match.

Consult the Boost Regex Perl Regular Expression Syntax page to make the building of the expression easier.

REGEX_Match(string,pattern,icase): Searches a string for an occurrence of a regular expression.

Tells if the string matches the pattern from the first character to the end. To look for something that does not necessarily start at the beginning of the string, start the pattern with '.*'. To look for something that does not necessarily go all the way to the end of the string, end the pattern with '.*'.

Consult the Boost Regex Perl Regular Expression Syntax page for more information on how to properly construct a regular expression.

icase is an optional parameter. When specified, the case must match. By default icase=1 meaning ignore case. If set to 0, the case must match.

Example

REGEX_Match(123-45-6789, "\d{3}-\d{2}-\d{4}") returns -1 (True)

REGEX_Replace(string, pattern, replace,icase): Allows replacement of text using regular expressions and returns the string resulting from the RegEx find pattern and replace string. All occurrences of the match are replaced, not just the first.

Consult the Boost Regex Perl Regular Expression Syntax page to make the building of the expression easier. The replace parameter can be either a specified value as shown below, or a marked group, such as "$1"

icase is an optional parameter. When specified, the case must match. By default icase=1 meaning ignore case. If set to 0, the case must match.

Example

REGEX_Replace("Don't reveal your Social Security number, 123-45-6789","\d{3}-\d{2}-\d{4}", "CLASSIFIED") returns Don't reveal your Social Security number, CLASSIFIED

REGEX_Replace("Change all domain names from alteryx@Alteryx.com","@.*\.", "@extendthereach.") returns Change all domain names from alteryx@extendthereach.com

Replace(Str, Target, Replacement): Returns the string (Str) after replacing each occurrence of the String (Target) with the String (Replacement).

Example

Replace("Good judgment comes from experience", "experience", "awareness") returns "Good judgement comes from awareness"

ReplaceChar(x, y, z): Returns the string (x) after replacing each occurrence of the character (y) with the character(z). If the replacement character (z) is a string with more than one character, only the first one is used. If (z) is empty, each character (x) that matches any character in (y) is simply removed.

Example

ReplaceChar("abcdefb", "b", "_") returns "a_cdef_"

ReplaceChar("@a#b%c", "@,#,%", "_") returns "_a_b_c"

ReplaceFirst(Str, Target, Replacement): Returns the string (Str) after replacing the first occurrence of the string (Target) with the string (Replacement).

Example

ReplaceFirst("abcdefb", "b", "_") returns "a_cdefb"

ReverseString(Str): Reverses all the characters in the string.

Example

ReverseString("abcdefb") returns "bfedcba"

Right(String, len): Returns the last (len) characters of the string. If len is less than 0 or greater than the length of String, the string remains unchanged.

Example

Right("92688", 3) returns a value of "688"

StartsWith(String, Target, CaseInsensitive=1): Checks if a string starts with a particular string. Returns a TRUE or FALSE if the string matches the pattern. It is defaulted to case insensitive.

The StartsWith function is case insensitive while the FindString function is case sensitive.

Example

StartsWith('ABC123', 'ABC') returns TRUE

StartsWith('ABC123', 'abc') returns TRUE

StartsWith('ABC123', 'abc', 0) returns FALSE

STRCSPN(x, y): Returns the length of the initial segment of the string (x) consisting of characters NOT in the string (y)

Example

STRCSPN("Bob's Amaco", "~!@#$%^&*'()") returns 3.  This is a useful test to make sure there is no punctuation in the string.

StripQuotes(x): Removes a matched set of quotation marks or apostrophes from the ends of the string

Example

StripQuotes("Hello there") returns Hello there

StripQuotes("'Hello there,' she said.") returns 'Hello there,' she said.

StripQuotes('"Hello there," she said.') returns "Hello there," she said.

STRSPN(x, y): Returns the length of the initial segment of the string [x] consisting of characters in the string [y]

Example

STRSPN("3034408896x105", "0123456789") returns 10.  This is a useful test to make sure a string consists of a set of characters.

Substring(x, start, length): Returns the substring of (x) starting at (start), stopping after(length), if provided

Example

Substring("949-222-4356", 4, 8) returns "222-4356"

Substring("949-222-4356", 4, 6) returns "222-43"

Substring("949-222-4356", 4) returns "222-4356"

TitleCase(x) : Converts a string to Title case

Example

TitleCase("john smith") returns "John Smith"

Trim(x, y): Removes the character(s) in the string y from the ends of the string x. Y is optional and defaults to trimming white space. Notice in the TRIM function examples the specified characters are trimmed. It doesn't matter what order the characters are in.

Example

Trim("!see instructions!!!", "!") returns "see instructions"

Trim("   Test123   ") returns "Test123"

TrimLeft(x, y): Removes character in the string y from the beginning of the string x. Y is optional and defaults to trimming white space

Example

TrimLeft("** special invitation ", " *") returns "special invitation "

TrimRight(x, y): Removes character in the string y from the end of the string x. Y is optional and defaults to trimming white space

Example

TrimRight("John Smith   ") returns "John Smith"

TrimRight("John Smith**","*") returns "John Smith"

While you may be passing in a string of characters to trim, the TRIM functions do not respect the order of the characters; it treats the string as a "list." Therefore it will trim ALL of the characters in the list. If you are looking to replace a string, use a REPLACE function or a REGEX function in your expression.

Uppercase(x)

Converts a string to upper case

About Length and Strings: When referencing the LENGTH of a string the first character is counted as 1 (the length of the following string "record" is 6).

HOWEVER, when referencing the character position within a STRING, positions are actually counted BETWEEN characters. So think of the first character's position as 0.  Therefore, the position of the letter "c" in the string "record" is at position 2.

Example

Uppercase("John Smith") returns "JOHN SMITH"

UuidCreate(): Creates a Unique identifier.

Example

UuidCreate() returns a unique value such as ba131836-1ba3-4d42-8f7e-b81a99c2e838

A test function performs validation tests on data. Use a test function to identify the data type of a value or determine if a value exists. A test function can be used on all data types.

CompareDictionary(a,b): Compares two strings in dictionary order. Returns -1 if a<b, 0 if a==b, 1 if a>b

Example

CompareDictionary (apples, bananas) returns -1

CompareDictionary (cherries, bananas) returns 1

CompareDictionary (bananas, bananas) returns 0

CompareDigits(a,b, nNumDigits): Compares two numbers and determines if they are the same to the given number of digits. The comparison finds the difference between the numbers and reports A and B the same when the leading digit of the difference is NumDigits or more places to the right of the digits of the larger of A and B.

The NumDigits argument must not be null, and must be between 1 and 19, otherwise an error will result. If a non-integer is given, it is rounded to the nearest integer. (NumDigits can be between 0.5 and 19.499)

Example

CompareDigits(12345, 12444, 3) returns "True" (because the difference is 99, and its leading digit is 3 places to the right of the leading digit of A)

CompareDigits(12345, 123445, 3) returns "False" (because the difference is 100, and its leading digit is only 2 places to the right of the leading digit of A)

CompareDigits(12.345, 12.347, 3) returns "True" (because the difference is 0.002, and its leading digit is 4 places to the right of the leading digit of A)

CompareDigits(12.345, 12.435, 3) returns "True" (because the difference is 0.09, and its leading digit is 3 places to the right of the leading digit of A)

CompareDigits( .91234, .91334, 3) returns “False” (because the difference is .001, and its leading digit is only 2 places to the right of the leading digit of A)

CompareEpsilon(a,b,epsilon): Compares two floating point numbers and returns true if they are within epsilon.

Example

CompareEpsilon([123.456789101112], [123.456789101114], 0.0001) returns "True"

CompareEpsilon([123.456], [123.456789101112],0.0001) returns "False"

IsEmpty(v): Tests if v is NULL or equal to "" or not

Example

Name	IsEmpty
John	False
	True
Mary	False
[Null]	True

IsInteger(v) : Tests if v contains a value that can be converted to an integer. If so, it will return true.

Example

Value	IsInteger
1	True
1.23	False
B	False

IsNull(v) : Tests if v is NULL or not

To populate a record with a NULL value, use the function NULL()

Example

Name	IsNull
John	False
[Null]	True
Mary	False

IsNumber(v) : Tests if the field type for v is a number or not.

IsSpatialObj(v): Tests if the field type for v is a spatial object or not.

IsString(v): Tests if the field type for v is a string or not.