IMPORTANAT FUNCTIONS IN ALTERYX

CONDITIONAL
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
 Result Class

1 Gold

3 Bronze

4 Tin

2 Silver

1 Gold

Null Other

OPERATORS

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

STRING
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

ReplaceFirst(Str, Target, Replacement): Returns the string (Str) after replacing

the first occurrence of the string (Target) with the string (Replacement).

Example

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

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