◄Show Table of Contents

Functions

Certain tools have an Edit Formula box from their Configuration Properties, allowing you to select particular variables from the input stream to conduct analysis on, by creating expressions and formulas for data output.

Tools that have this option are Formula,Filter,Generate Rows, Multi-Row Formula

From the Functions tab, expand one of the data trees to create the formula. Either double click on the function name or click and drag it into the Expression Box. For a list of variables to add to the formula click the Variables tab. For more information on how to save or load custom expressions, see the Saved Expressions page.

Types of functions

Conditional

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

Example

IIF([CUSTOMER], "send invitation", "send information")

            [CUSTOMER]field must be a Boolean field.

If the [CUSTOMER] field value is TRUE, then the value that is returned is "send invitation".

If the [CUSTOMER] field value is FALSE, then the value that is returned is "send information".

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

Example

IF [Class]==1 THEN

"Gold"

ELSE

"Other"

ENDIF

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

Example

IF [Class]==1 THEN

"Gold"

ELSEIF [Class]==2 THEN

"Silver"

ELSE

"Other"

ENDIF

SWITCH Compare a value against a list of Cases and return the corresponding result.

Example

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

back to top

Conversion

BinToInts(s) Converts the binary strings to an integer

CHARFROMINT(x) Returns the character that is Unicode # x

CHARTOINT(S) Returns the unicode # of c

ConvertFromCodepage(s, codePage) Translates text from a code page to Unicode.

ConvertToCodepage(s, codePage) Translates text from Unicode to a specific code page.

HEXTONUMBER(x)Converts a HEX string to a number

Example

HEXTONUMBER(dd)is converted to the number 221

IntToBin(x) Converts n to a binary string

IntToHex(n) Converts n to a hexidecimal string

TONUMBER(x, bIgnoreErrors, keepNulls) Converts a string parameter to a number. The second parameter is optional and allows for ignoring conversion error messages. This parameter is a boolean flag and will accept a value of 1,0, true or false. There is an optional 3rd parameter to handle Nulls.

Optional parameters:

bIgnoreErrors: 0 or false (default) means it will report conversion error messages; 1 or true means it will ignore conversion errors.

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

Example

TONUMBER("878")returns the String 878 as a number.

TONUMBER("Number", "false") will report: ConvError: Formula: TONUMBER: Number lost information in translation

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

Note: When converting Null values to a number, a 0 will replace a Null value.

TOSTRING(x, numDec, addCommas)Converts a numeric parameter to a string using [numDec] decimal places. There is also a third parameter to return the appropriate comma.

Optional Parameters:

addCommas: 0 or false (default) means format the numeric string without commas; 1 or true means format with commas.

Example

TOSTRING(10, 0)returns "10" as a text string

TOSTRING(10.2, 2)returns "10.20" as a text string

TOSTRING(1000.2, 2, 1)returns "1,000.20" as a text string

back to top

File

FileAddPaths(Path1, Path2) Adds 2 file path parts, making sure there is exactly 1 "\" 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 Path (including the .).

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"

back to top

Finance*

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

FinanceFVSchedule(Principle, Year1Rate, Year2Rate) Apply a series of interest rates to an investment

FinanceIRR(Value1, Value2) Internal Rate of Return on an investment:

The Internal Rate of Returnof an investment is 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 of an investment

The Modified Internal Rate of Return is a modification of the internal rate of return and as such 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 (e.g. 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

Present value is 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

*Finance function definitions came from Wikipedia: http://en.wikipedia.org

back to top

Math

Integer: The following are integer functions included in the formula library:

BinaryAnd(n,m) Returns a Binary of n and m

Example:

BinaryAnd(1,1) returns 1; (1,0) returns 0

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.

BinaryNot(n) Returns a Binary Not of n

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

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

Mod(n,d) Modulo of n divided by d

The Modulo operation finds the remainder of division of one number by another number.

Example

MOD(6,3)returns0;MOD(6,4)returns2

ShiftLeft(n,b) Left Shifts n (as Integer) by b Bits

ShiftRight(n,b) Right Shifts n (as Integer) by b Bits

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

Example

ABS(x)Absolute value of [32] is 32

ABS(x)Absolute value of [-32] is 32

ACOS(x) Arc Cosine of [x]

ASIN(x) Arc Sine of [x]

ATAN(x)ARC - Tangent of [x]

ATAN(x)returns the inverse tangent (arctangent) for each element of X. For real elements of X,ATAN(x)is in the range .

The atan function operates element-wise on arrays. The function's domains and ranges include complex values. All angles are in radians.

ATAN2(x)ARC - Tangent of [y/x]

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

Example

Average(20,30,55)Average returned is35as [(20+30+55)/3]= 35

CEIL(x) Return smallest integer greater than or equal to [x]. Works like the 'RoundUp' function in Excel.

Example

CEIL(1.1)returns 2

CEIL (6.54)returns7

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) Return 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 1.146128

LOG10(x) Base 10 logarithm of [x]

Example

LOG10(1)returns 0

LOG10(90)returns 1.954243

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

Example (5, 4, 3, 7, 6) would return 5

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

Example

PI()returns 3.141593

POW(x, e) Return [x] raised to the [e] power

Example

POW(2, 3)Is equivalent to 2³. The value returned is 8.

RAND() Returns a random number between 0 and 1

Example

RAND() will return a random number like 0.256

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

Example

RandInt(5000)will return a random whole number between 0 and 5000, like 741

Randint(10) can return 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 or 10

ROUND(x, mult) Return [x] rounded to nearest multiple of [mult]

Example

ROUND(55.34, 2)returns a value of 56

more examples below:

SIN(x) Sine of [x]

SINH(x) Hyperbolic Sine of [x]

SMARTROUND(x) Return [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]

back to top

Min/Max

BOUNDIf (x<min) return min; else if (x > max) return max; else return x;

MAX(v0, v1,..., vn) Returns the value with the highest count from a series of variables.

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

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

back to top

Operators

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

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

Operator	Description	Example
+	Addition	1 + 1
-	Subtraction	25 20
*	Multiplication	2 * 5
/	Division	10 / 5
(	Open Parenthesis	(2 +5 ) / 7
)	Close Parenthesis	(2 +5 ) / 7
AND	Operator 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.	[Match Status] =="Exact" AND "GeoLevel " == "Street"
&&	Same use as the AND operator.	[Match Status] =="Exact" && [GeoLevel] == "Street"
NOT	The NOT operator accepts one input. If that input is TRUE, it returns FALSE, and if that input is FALSE, it returns TRUE.	NOT([GEOCODED])   If the Boolean field [GEOCODED] is TRUE, then it returns FALSE.
!	Same use as the NOT operator.	!([GEOCODED])
OR	If either (or both) of the two values it checks are TRUE then it returns TRUE.	[GeoLevel]== "Street" OR [GeoLevel]== "Intersection"
||	Same use as the OR operator.	[GeoLevel]== "Street" || [GeoLevel]== "Intersection"
IN	Determines if a given value matches any value in a subquery or a list.	[GeoLevel] IN ("Street", "Intersection")
NOT IN	Determines if a given value matches any value not in a subquery or a list.	[GeoLevel] NOT IN ("Street", "Intersection")
=	Equal to	[GeoLevel]= "Street"
==	Equal to	[GeoLevel]== "Street"
>	Greater than	[POPCY] > 100000
>=	Greater than or Equal to	[POPCY] > 100000
<	Less than	[POPCY] < 100000
<=	Less than or Equal to	[POPCY] <= 100000
!=	Not Equal to	[CITY] != "Springfield"

back to top

Spatial

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

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

This function returns a numeric data value.

ST_Boundary(object) returns the boundary of the spatial object.

This function returns a polyline spatial object indicating the boundary of the input polygon.

ST_BoundingRectangle(object, ...) returns the bounding rectangle of the spatial object.

This function returns a polygon spatial object.

ST_Centroid(object) returns the centroid of the spatial object.

This function returns a point spatial object.

ST_CentroidX(object) returns the longitude of the centroid of the spatial object.

This function returns a numeric data value.

ST_CentroidY(object) returns the latitude of the centroid of the spatial object.

This function returns a numeric data value.

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

This function returns a spatial object.

ST_Contains(object1,object2) Returns True if object1 contains object2

This function returns a Boolean value.

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

This function returns a spatial object.

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

This function returns a spatial object.

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

This function returns a spatial object.

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

This function returns a spatial object.

ST_Cut(object1,object2) Returns the result of cutting object1 from object2.

This function returns a spatial object.

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.

This function returns a 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.

This function returns a numeric data value.

ST_EndPoint(object) returns the last point of the spatial object.

This function returns a point spatial object.

ST_Intersection(object1, object2,...) returns the intersection of the specified spatial objects

This function returns a  spatial object

ST_Intersects(object1, object2...) returns True if the spatial objects intersect.

This function returns a Boolean value.

ST_InverseIntersection(object1, object2...) returns the inverse intersection of the specified spatial objects.

This function returns a spatial object.

ST_Length(object, units) returns the linear length of the spatial object.

This function returns a numeric data value.

ST_MaxX(object) returns the maximum longitude of the spatial object.

This function returns a numeric data value.

ST_MaxY(object) returns the maximum latitude of the spatial object.

This function returns a numeric data value.

ST_MinX(object) returns the minimum longitude of the spatial object.

This function returns a numeric data value.

ST_MinY(object) returns the minimum latitude of the spatial object.

This function returns a numeric data value.

ST_NumParts(object) returns the number of parts in the spatial object

This function returns a numeric data value.

ST_NumPoints(object) returns the number of points in the spatial object.

This function returns a numeric data value.

ST_ObjectType(object) returns the spatial object type as a string value.

This function returns a string data value.

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

This function returns a point spatial object.

ST_RandomPoint(object) returns a random point within the spatial object.

This function returns a point spatial object.

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

This function returns a Boolean value.

ST_StartPoint(object) returns the first point in the spatial object.

This function returns a point spatial object.

ST_Touches(object1, object2) returns True if object 1 touches object 2

This function returns a Boolean value.

ST_TouchesOrIntersects(object1, object2) returns True if object 1 touches or Intersects object 2.

This function returns a Boolean value.

ST_Within(object1, object2) returns True if object 1 is contained by object 2.

This function returns a Boolean value.

back to top

Specialized

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

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 -->Enviornment Variables. A list of System Variable appears. Any value from this can be used.

Example GetEnvironmentVariable(CommonProgramFiles) would return C:\Program Files\Common Files

GetEnvironmentVariable(OS) would return Windows_NT

Note: The GetEnviornmentVariable is not supported in apps published to the web.

RANGEMEDIAN(...) Calculates the median from a series of aggregated ranges. See Range Median documentation for details.

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:

Examples:

• 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 1st 4 digits or the Soundex if none. If there are digits (numbers) in the String, the first 4 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(...)

Example user wants to return the top 5 of a stream. N is the starting point, V0 is the indexed field (subsequent Variables are optional), and VN is the goal - top 5, configuration would look like:

TOPNIDX(0, [IndexedField], 5)

NULL() Returns a Null value

UrlEncode(String) Encodes string as a web legal URL

Example:

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

String

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

FINDSTRING(Str,Target ) Searches for theoccurrenceof a particular string within a data field and returns the numeric position of its occurrence in the string.

Returns the first 0-based index of the 1st occurrence of Target in Str. -1 if none.

Example

FINDSTRING("A bumble bee is considerably faster than a tractor","bee")returns a value of 9.

FINDSTRING("A bumble bee is considerably faster than a tractor","orange") returns a value of -1.

Note: For more information on the FINDSTRING function, see Search for a specific string.

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

Example

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

LENGTH(x) Return 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.

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

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

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.  Returns a TRUE or FALSE if the string matches the pattern. Consult the Boost Regex Perl Regular Expression Syntax page for more information on how to properly construct a regular expression.

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.

Example

REGEX_MATCH(My quote starts Right Here,'.*".*".*') Returns -1 (True)

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

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.

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 [x] 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].

Example

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

Note: [y] could also be a list of characters, where any character in the list gets replaced:

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

REPLACEFIRST(Str, Target, Replacement) Returns the string Str after replacing the 1st 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

REVERSTRING("abcdefb")returns"bfedcba"

RIGHT(x, len) Return the last [len] characters of the string [x].

Example

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

STRCSPN(x, y) Return 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) Remove a matched set of quotation marks from the ends of the string

Example

STRIPQUOTES("Hello There") returns Hello There

STRIPQUOTES("Why, 'Hello There,' she said") returns Why, 'Hello there,' she said

STRSPN(x, y) Return 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) Return the substring of [x] starting at [start], stopping after [length], if provided

Example

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

TITLECASE(x) Converts a string to Title case

Example

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

TRIM(x, y) Remove 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.

Examples

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

TRIM("   Test123   ")returns "Test123"

TRIMLEFT(x, y) Remove 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) Remove 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"

Note: 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

Example

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

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.

UuidCreate() Creates a Unique identifier.

Example

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

back to top

Test

CompareDictionary(a,b) Compares 2 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 2 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 2 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

Record Number	Name	Address	City	State
1	John	123 Main St	Springfield	Oh
2		456 King St	Smallville	UT
3	Mary	789 Maple St	Waterloo	IA

 

ISInteger(v) Tests if v is an integer or not.

Example

Value	IsInteger
1	True
2	True
B	False

 

ISNULL(v) Tests if v is NULL or not

Example

Record Number	Name	Address
1	John	123 Main St
2	[Null]	456 King St
3	Mary	789 Maple St

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

ISInteger(v) Tests if v is contains a value convertible to an integer. If so, it will return true.

Example

Value	IsNumber
1	True
1.23	False
B	False

ISNumber(v) Tests if 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.

 

 

 

back to to

 

 

                  Related Topics

Send comments on this topic to the Alteryx Community.

Alteryx, Inc. * 230 Commerce * Suite 250 * Irvine, CA * 96202-1338 * Telephone: (888) 255-1207 *www.alteryx.com *