Create Functions

A Function is a piece of processing logic that can transform, filter or validate your data.

Think of a Function as an action (simple or complex) that can be applied to one or more inputs to create an output. For example, a Function can trim or append a value that meets certain criteria.

Data Studio comes with a variety of native (default) Functions that can be used as they are, or as building blocks for more complex, custom Functions.

Within Workflows, Functions are used in the Validate, Filter, Split and Transform steps. To see all the Functions available to you, go to the Functions page and select Filter by Type: Native.

The built-in Functions are split into categories to make them easier to find, based on the type of operation you want to perform.

Functions can also be combined. For example, you could transform a value using regex before validating that it meets certain criteria.

Native Functions by category

Functions related to data cleanup.

Function Description Example(s)
Example phone number Generates an example phone number for a specified region in various formats. N/A
Format phone number Formats a phone number or extract specific attributes. N/A
Formats phone number (dynamic) Format a phone number or extract specific attributes, with country code supplied dynamically. The country code must be in the CLDR two-letter region-code format as described in the CLDR Territory Information table. N/A
Jaro edit measure Calculates the Jaro-Winkler distance between the input value and the compare value returning a percentage match. The higher the match the more similar the values are. N/A
Jaro-Winkler edit measure Calculates the Jaro-Winkler distance between the input value and the compare value returning a percentage match. The higher the match the more similar the values are. The Jaro-Winkler algorithm is the variant of Jaro algorithm and includes additional checks which test for a common prefix at the start of both the values. N/A
Levenshtein distance Calculates the Levenshtein edit distance between the input value and a compare value returning the number of edits required to transform the input value to the compare value and vice-versa. The edit distance between Frank and Plank is 2, because two characters must be changed to go from Frank to Plank and vice-versa. Edits are not just transpositions but also removals and insertions.
Levenshtein distance percentage Calculates the Levenshtein edit distance between the input value and the compare value returning a percentage similarity between the two values. N/A
Parse Parses a value and extracts values that match supplied format patterns or regular expressions. This Function can be used to extract values that look like an expected type of value from a free-text input. Take an embedded postal code in an address string. The result can be a deduplicated, comma separated and quoted list of values for each result.
Multiple patterns can be searched at the same time with each search performed in sequence.

Parse by format example
Input: abc1234def5678
Format pattern to search for: 9999
Result: 1234,5678

Parse by regular expression example
Input: abc1234abc5678
Expression to search for: a.c
Result: abc,abc
Phone number match code Returns a match code for comparison of two phone numbers: EXACT_MATCH, NSN_MATCH (including extensions), SHORT_NSN_MATCH (ignoring extensions) or NO_MATCH. N/A
Phone number match code (dynamic) Returns a match code for comparison of two phone numbers: EXACT_MATCH, NSN_MATCH (including extensions), SHORT_NSN_MATCH (ignoring extensions) or NO_MATCH, with a country code supplied dynamically. The country code must be in the CLDR two-letter region-code format as described in the CLDR Territory Information table. N/A
Remove noise Returns the input value after standardizing it by removing noise from the value. The value is transformed by removing all characters except letters, numbers, and whitespace. Additional operations can be selected and applied in a cumulative manner:
  • Remove vowels - Removes the letters a, e, i, o, and u.
  • Remove digits - Removes any numeric characters.
  • Remove whitespace - Remove all whitespace characters
  • Remove alphabetic - Removes all alphabetic characters (including vowels).
Input: abc defgh123 456+-_*

Remove vowels result: bc dfgh123 456
Remove digits result: abc defgh
Remove whitespace result: abcdefgh123456
Remove alphabetic result: 123 456

Functions for converting data from one form into another.

Function Description Example(s)
Auto convert Automatically infers the data type. N/A
Datatype Returns the data type of the input value. Input: abc_123
Result: ALPHANUMERIC
Length Returns the number of characters in the input value. Input: abc_123
Result: 7
To alphanumeric Converts the data type of any value to an alphanumeric one. Input: 32 (Number)
Result: 32 (Alphanumeric)
To date Converts any value to a date. Input: 25.12.2021 (Alphanumeric)
Result: 25-Dec-2021 (Date)
To empty Replaces all values with empty strings. N/A
To null Converts a value only made up of whitespace characters to null. Input: A value with three characters, space + tab + space(Alphanumeric)
Result: A null value
To number Extracts only the leading integer or decimal from the start of an input value and converts it to a number. Returns an error where a number can't be extracted from the start of the input. Input: 012 (Alphanumeric)
Result: 12 (Number)

Input: 3.4ab5 (Alphanumeric)
Result: 3.4 (Number)

Input: c6 (Alphanumeric)
Result: Invalid number (Error)
To spaces Converts any null (empty) values to blank ones (spaces). N/A
To time Converts any value to a time period. Input: 00-00-23
Result: 00:00:23

Date and time related Functions.

Function Description Example(s)
Add period date Add a time period to a date/time value. Input: 16/02/2023
Time period type: Days
Period size: 90
Result: 17-May-2023
Compare date/time Compares two date/time values, returning the difference between them in the defined units (Millis, Seconds, Minutes, Hours, Days, Weeks, Months, Years / Age, Century). First date/time input: 25/01/2023
Second date/time input: 17/08/2022
Period type: Days
Result: 161
Convert to date Attempts to convert an alphanumeric value to a date. Input: 25.1.2022
Result: 25-Jan-2022The Function includes settings to define how the year is resolved if only a two-digit year is provided, for example 25.12.49:
  • Century break: For dates with two-digit-years, this setting will tell us which century to assume the year refers to. The default value of 50 means that 25.12.49 will be converted to 25-Dec-2049, and 25.12.51 will be converted to 25-Dec-1951.
  • Watershed is floating: If checked (floating watershed is turned on), the century break year goes up with the current year, so setting century break year to 50 with floating means the watershed is 50 years behind the current year. In 2021 this means that 1.1.71 will resolve to 1-Jan-2071, and 1.1.72 to 1-Jan-1972.
  • Force century: A numeric values between 0 and 99 that, when set, overrides the century break and floating watershed settings and forces all two-digit years to be converted to use the defined century only. For example, setting Force century to 20 will mean all dates with two-digit years will be converted to dates between 2000 and 2099.
Create date/time stamp Creates a time stamp value with a supplied year, month, day, hours, minutes and seconds. Input year: 2022
Input month: 1
Input day: 25
Input hours: 8
Input minutes: 12
Input seconds: 25

Result: 25-Jan-2022 08:12:25
Format date Formats a date/time with custom date format.The letters that can be used in a date and time pattern are (note that casing is important):
  • G: Era (AD)
  • y: Year
  • Y: Week year
  • M: Month in year
  • w: Week in year (1-53, week 1 starts on the first Monday of the year)
  • W: Week in month (0-5, week 1 starts on the first Monday of the month)
  • D: Day in year (1-366)
  • d: Day in month (1-31)
  • F: Day of week in month
  • E: Day name in week
  • a: AM/PM marker
  • H: Hour in day (0-23)
  • k: Hour in day (1-24)
  • K: Hour in AM/PM (0-11)
  • h: Hour in AM/PM (1-12)
  • m: Minute in hour
  • s: Second in minute
  • S: Millisecond
Pattern letters can often be repeated, and the number will determine the exact presentation format. For example:
  • y or yyyy will display the four-digit year "2023", yy will display the two-digit year "23".
  • M will display "7", MM "07", MMM "Jul", and MMMM "July".
  • E will display "Tue", EEEE will display "Tuesday".
Text can also be added using single quotes ('). Allowed separators are spaces and other punctuation (such as commas, full stops, semicolons). For example: yyyy-MM-dd'T'HH:mm:ss.SSS will display as "2020-11-10T13:20:19.124".
Input: 16/01/2023
Date format string: E, dd MMMM yy
Result: Mon, 16 January 23 (with English locale setting)

Input: 16/01/2023 12:34:56.789
Date format string: yyyy-MM-dd'T'HH:mm:ss.SSS
Result: 2023-01-16T12:34:56.789
Get age Returns the number of years between the supplied date and the current date. Input: 01/01/2000
Result: 23 (if the function is evaluated in 2023)
Get century Extracts the century from the supplied date. Input: 16/02/2023
Result: 21
Get day of week Extracts the day of the week (integer from 1-7, Monday is 1) from the supplied date. Input: 16/02/2023
Result: 4
Get day of week name Extracts the name of the day from the supplied date. Input: 16/02/2023
Result: Thursday
Get days Extracts the day from the supplied date. Input: 16/02/2023
Result: 16
Get hours Extracts the hour from the supplied date/time. Input: 16/01/2023 12:34:56
Result: 12
Get millis Extracts the millisecond from the supplied date. Input: 16/01/2023 12:34:56.789
Result: 789
Get minutes Extracts the minute from the supplied date/time. Input: 01/01/22 12:31
Result: 31
Get month name Extracts the name of the month from the supplied date. Input: 01/01/22 12:31
Result: January
Get months Extracts the month number (1-12) from the supplied date. Input: 01/01/22 12:31
Result: 1
Get seconds Extracts the second from the supplied date/time. Input: 16/01/2023 12:34:56
Result: 56
Get weeks Extracts the week of the year number from the supplied date. This calculation assumes that the first week of the year starts on the first day of the year, whatever day of the week that falls on. Input: 21/01/2023
Result: 3
Get years Extracts the year from the supplied date. Input: 01/01/23
Result: 2023

Functions with results dynamically changing based on context.

Function Description Example(s)
Current row Returns the row ID, starting at 1, for the currently displayed row. N/A
Current timestamp (dynamic) Returns the current timestamp. N/A
Get cell Returns a value from a cell at a given row/column position from the source of current drilldown. N/A

Functions that apply encoding to data.

Function Description Example(s)
Decode geohash coordinates Converts a geohash to its latitude and longitude components. N/A
Escape Escapes or un-escapes a string based on a given escaping style. N/A
Geohash coordinates Converts latitude and longitude to a geohash. N/A
Hash code Returns a generated hash code for the input value based on the specified algorithm. N/A

Functions that validate against pre-defined regular expressions (regex).

Function Description Example(s)
Is AMEX card format Indicates whether the input has the format of an American Express card number. N/A
Is bank RTN number format Indicates whether the input has the format of a bank routing transit number (RTN) which consists of 9 digits. 123456789
Is CSS hex code format Indicates whether the input has the format of a Hexadecimal (hex) color value which is alphanumeric and starts with a #. #FFCC99
Is Canadian postal code format Indicates whether the input has the format of a Canadian postal code. N/A
Is Companies House number format Indicates whether the input has the format of a Companies House number. N/A
Is Discovery card format Indicates whether the input has the format of a Discovery card number. N/A
Is EU VAT number format Indicates whether the input has the format of a European Union Value Added Tax (EU VAT) number. N/A
Is French VAT number format Indicates whether the input has the format of a French Value Added Tax (VAT) number. N/A
Is French phone number format Indicates whether the input has the format of a French phone number. N/A
Is French postal code format Indicates whether the input has the format of a French postal code. N/A
Is German postal code format Indicates whether the input has the format of a German postal code. N/A
Is IP address format Indicates whether the input has the format of an Internet Protocol (IP) address. N/A
Is ISBN format Indicates whether the input has the format of an International Standard Book Number (ISBN) which consists of 13 digits. N/A
Is ISO date format Indicates whether the input has the format of an International Organization for Standardization (ISO) date which starts with the year, followed by the month and day. 2022-06-23
Is international phone number format Indicates whether the input has the format of an international phone number. N/A
Is Mastercard format Indicates whether the input has the format of a Mastercard number. N/A
Is time 24hr format Indicates whether the input has the format of a 24 hour time. N/A
Is UK National Insurance Number (NIN) format Indicates whether the input has the format of a UK National Insurance Number (NIN) which consists of 2 prefix letters, 6 digits and one suffix letter. AA123456C
Is UK VAT number format Indicates whether the input has the format of a United Kingdom Value Added Tax (UK VAT) number which consists of letters GB followed by 9 digits. GB123456789
Is UK mobile number format Indicates whether the input has the format of a United Kingdom mobile/cell phone number. Input: 07712345678
Result: true

Input: 077123456789
Result: false
Is UK passport number format Indicates whether the input has the format of a United Kingdom passport number. Input: 999999999
Result: true

Input: A99999999
Result: false
Is UK post code format Indicates whether the input has the format of a United Kingdom post code. Input: SW1A 1AA
Result: true

Input: AA99 9AA
Result: false
Is UK phone number format Indicates whether the input has the format of a United Kingdom landline phone number. N/A
Is UK vehicle registration number Indicates whether the input has the format of a United Kingdom vehicle registration number which consists of 2 letters, 2 numbers, a space and 3 further letters. Example: AB12 CDE
Is US SSN format Indicates whether the input has the format of a United States Social Security Number (US SSN). AAA-GG-SSSS
Is US state code format Indicates whether the input has the format of a United States state code. N/A
Is US phone number format Indicates whether the input has the format of a United States landline phone number. N/A
Is US ZIP code format Indicates whether the input has the format of a United States Zone Improvement Plan (ZIP) code. 90210
Is Visa card format Indicates whether the input has the format of a Visa card number. N/A
Is credit card format Indicates whether the input has the format of a credit card number. N/A
Is email address format Indicates whether the input has the format of an email address. N/A
Is passport number format Indicates whether the input has the format of a passport number. N/A
Is web URL format Indicates whether the input has the format of a website's address or the Uniform Resource Locator (URL). www.edq.com

Functions that provide information on their input.

Function Description Example(s)
Error message Returns an error message, setting the expression into error and displaying the result in red. N/A
Warning message Returns a warning message and displays the result in yellow. N/A

Functions that operate on a list.

Function Description Example(s)
Combine lists Joins two lists of values in different ways. Possible operations are:
  • Difference - The symmetric difference, leaving the values from the left and right that don't intersect.
  • Intersection - The intersection of the two lists leaving only values that match on both sides.
  • Remove left - The values from the right list that don't intersect the left list.
  • Remove right - The values from the left list that don't intersect the Right list.
  • Union - The two lists combined.
N/A
Common in list Analyzes a list and returns two values: the most or least common in the list and the frequency of occurrence.
  • Least common in list - Returns a list of: the least common value and the number of times it occurs.
  • Most common in list - Returns a list of: the most common value and the number of times it occurs.
N/A
Expand list Extracts all members of a list and separates them with a specified delimiter. N/A
Extract from list Parses a value that is a list of comma-separated values, allowing any value to be extracted by the index position in the list. N/A
First non null Given a variable number of input values, returns the first value that is not null (empty). N/A
List Transforms a list of values using a variety of operations. Multiple operations can be used cumulatively.
  • Common Prefix - Compares all supplied values and returns the prefix that's common to all of them.
  • Deduplicate - Deduplicates all values in a list.
  • Reverse - Reverses all values in a list
  • Reverse sort - Reverse sorts all values in a list.
  • Sort - Sorts all values in a list.
N/A
List frequency Returns unique values in a list interleaved with their count (frequency). N/A

Functions for controlling logic.

Function Description Example(s)
And The logical operator AND. If all of the inputs equate to true, returns TRUE. N/A
If then else Checks the condition field for the value of TRUE and if so, returns the condition met field, otherwise else. Can also have multiple condition fields to be evaluated if the precursory condition is not met. N/A
Not Turns the input value from TRUE to FALSE or vice versa. N/A
Or The logical operator OR. If any one of the inputs equates to true, returns TRUE. N/A

Functions that perform a lookup within other data.

Function Description Example(s)
Contains match Returns TRUE if any element of a list is contained in a lookup column.The following match types are available:
  • Case-insensitive - ignores the letter case.
  • Normalized - ignores the letter case and replaces accented characters with normalized versions (replaces è with e).
  • Standardized - as Normalized but also replaces punctuation with spaces.
  • Standardized: no digits - as Standardized but also removes numeric digits.
  • Exact - matches the exact letter case and format.
  • Format - matches the format only.
  • Regular expression - treats the lookup column values as regular expressions for matching purposes.
Three input types are available:
  • Space-separated - treat the input value as a single sentence of whitespace-separated words.
  • Whole value - treat the input value as a single sentence containing only one word (i.e. the whole value is the word).
  • Comma-separated - treat the input value a comma-separated list of sentences. Each sentence is treated as whitespace-separated words.
N/A
Extract matches as list Extracts elements from the input that are contained in a lookup column and outputs them as a list.The following match types are available:
  • Case-insensitive - ignores the letter case.
  • Normalized - ignores the letter case and replaces accented characters with normalized versions (replaces è with e).
  • Standardized - as Normalized but also replaces punctuation with spaces.
  • Standardized: no digits - as Standardized but also removes numeric digits.
  • Exact - matches the exact letter case and format.
  • Format - matches the format only.
  • Regular expression - treats the lookup column values as regular expressions for matching purposes.
Three input types are available:
  • Space-separated - treat the input value as a single sentence of whitespace-separated words.
  • Whole value - treat the input value as a single sentence containing only one word (i.e. the whole value is the word).
  • Comma-separated - treat the input value a comma-separated list of sentences. Each sentence is treated as whitespace-separated words.
N/A
Remove matches Removes elements from the input that are contained in a lookup column.The following match types are available:
  • Case-insensitive - ignores the letter case.
  • Normalized - ignores the letter case and replaces accented characters with normalized versions (replaces è with e).
  • Standardized - as Normalized but also replaces punctuation with spaces.
  • Standardized: no digits - as Standardized but also removes numeric digits.
  • Exact - matches the exact letter case and format.
  • Format - matches the format only.
  • Regular expression - treats the lookup column values as regular expressions for matching purposes.
Three input types are available:
  • Space-separated - treat the input value as a single sentence of whitespace-separated words.
  • Whole value - treat the input value as a single sentence containing only one word (i.e. the whole value is the word).
  • Comma-separated - treat the input value a comma-separated list of sentences. Each sentence is treated as whitespace-separated words.
N/A
Replace matches Replaces elements from a list with matching elements from a lookup table.The following match types are available:
  • Case-insensitive - ignores the letter case.
  • Normalized - ignores the letter case and replaces accented characters with normalized versions (replaces è with e).
  • Standardized - as Normalized but also replaces punctuation with spaces.
  • Standardized: no digits - as Standardized but also removes numeric digits.
  • Exact - matches the exact letter case and format.
  • Format - matches the format only.
  • Regular expression - treats the lookup column values as regular expressions for matching purposes.
Three input types are available:
  • Space-separated - treat the input value as a single sentence of whitespace-separated words.
  • Whole value - treat the input value as a single sentence containing only one word (i.e. the whole value is the word).
  • Comma-separated - treat the input value a comma-separated list of sentences. Each sentence is treated as whitespace-separated words.
N/A

Mathematical Functions.

Function Description Example(s)
Calculate Evaluates a simple mathematical calculation using +, -, /, * and %.
  • Add - Adds together two or more numeric values.
  • Divide - Divides two or more numeric values.
  • Multiply - Multiplies two or more numeric values.
  • Remainder - Produces the remainder value when a division is calculated.
  • Subtract - Subtracts one numeric value from another.
Input: 3
Add: 3
Multiply by: 10

Result: 33
Power of n Returns the nth power of the input value. Input: 3
The power of: 3

Result: 27

Number related Functions.

Function Description Example(s)
Round number Sets the number of decimal points after a decimal value. Input: 3.151
Decimal places: 1

Result (ceiling): 3.2
Result (half-up): 3.2
Result (floor): 3.1
Transform number Transforms a number using any of a wide selection of mathematical and scientific conversions
  • Absolute - Returns the absolute value of the integer
  • Arccosine - Returns the trigonometric arc cosine of the input as an angle in radians, in the range of 0 through to pi
  • Arcsine - Returns the trigonometric arc sine of the input as an angle in radians, in the range of -pi/2 through to +pi/2
  • Arctangent - Returns the trigonometric arctangent of the input as an angle in radians, in the range of -pi/2 through to +pi/2
  • Cosine - Returns the trigonometric cosine of an angle in radians
  • Cube - Returns the cube of a number
  • Cube root - Returns the cube root of a number
  • Euler raised - Returns Euler raised, e, raised to the power of input value
  • Log base 10 - Returns the logarithm of any number, to base 10
  • Natural log - Returns the natural logarithm of any number, which is the log to base e
  • Negate - Converts a positive number to negative and vice-versa
  • Precision - Returns a numerical precision of input value - a number of significant digits
  • Random integer - Returns a random integer between 1 and the inputted value
  • Random number - Returns a random number between 0 and the supplied maximum value
  • Scale - Returns a numeric scale of a value - a number of significant digits after the decimal
  • Sine - Returns the trigonometric sine of an angle in radians
  • Square root - Returns the square root of a number
  • Sum digits - Returns a sum of numeric digit of any value. Example:
    Input value: 546.2
    Sum digits: 17
  • Tangents - Returns the trigonometric tangent of an angle in radians
N/A

Uncategorized Functions.

Function Description Example(s)
Constant value Returns the specified value. N/A
Row count Returns the total number of rows. N/A
Set cell style Sets the color and/or tooltip of a cell. Style: None, Success, Warning, Failure

Sets the background cell color to none, green, yellow, or red

Functions for performing text manipulation.

Function Description Example(s)
After Extracts, from an input value, everything that follows a search value. Input: ABC123
Search value: ABC

Result: 123
Before Extracts, from an input value, everything that precedes a search value. Input: 123ABC
Search value: ABC

Result: 123
Chunk Breaks the input value into chunks using the supplied length, returning a list of split values. N/A
Common prefix Compares all supplied values and returns the prefix that is common to all of them. N/A
Concatenate Creates a new value from two values, concatenating them. N/A
Create list Creates a list from a number of values. N/A
Delimited substring Returns the substring of a value, using a start delimiter or an index position, and either an end delimiter or a return value length. Input: ABC,DEF,GHI
Delimiter: ,
Length: 5

Result: DEF,G
Difference Compares two values and returns the part that is the difference. Input one: a,b,c
Input two: a,b,c,d

Result: ,d
Index of difference Returns the position where two values begin to differ, starting from 1 or 0 if there's no difference. N/A
Initials Returns the initials by splitting words from the input value within optional list of delimiters (the default is whitespace). N/A
Insert Insert a value into the input value at a given position. N/A
Pad Pads any value with a chosen character to achieve a given overall length, with the option of putting the padding before (e.g. to add a leading zero) or after the value. N/A
Partition first Returns the list of 3 values ['before', 'fragment', 'after'], where 'before' is the part of input value before the first occurrence of the fragment and 'after' is what's left. A fragment is a regular expression. N/A
Partition last Returns the list of 3 values ['before', 'fragment', 'after'], where 'before' is the part of input value before the first occurrence of the fragment and 'after' is what's left. A fragment is a regular expression. N/A
Position Returns the first index position, starting from 1, of a search value within the input value. N/A
Regular expression replace Replaces any part of the input value that matches the search one with the replacement value. This Function differs from 'Replace' since the search value is a regular expression as opposed to a constant value. If there's no match, the input value is returned. Input: ABC::DEF:::::::::GHI
Search value: :+ (a regular expression which will match the colon symbol one or more times)
Replacement value: ;
Result: ABC;DEF;GHI

Capturing group matched values can be used in the replacement value. Input: AB1234CD
Search value: (AB)(\d{3})(.*)$
Replacement value: $3-$2
Result: 4CD-123 (the value of the third and second capturing groups, separated by a hyphen).
Repeat Returns an input value repeated a defined number of times. Input: ABC
Repeat: 3

Result: ABCABCABC
Replace Replaces all the instances of the search value with the replacement value. Input: ABC:DEF:GHI
Search value: :
Replacement value: ;

Result: ABC;DEF;GHI
Replace first Replaces first the instances of the search value with the replacement value. Input: ABC:DEF:GHI
Search value: :
Replacement value: ;

Result: ABC;DEF:GHI
Split Splits a value using another value in the field separator. N/A
Strings between Searches a value for substrings delimited by a start and end tag, returning all matching substrings in a list. N/A
Strip substring Strips the supplied substring value from the given input value if present. Input: abcdefghi
Search value: def

Result: abcghi
Substring Returns the substring of an input value, specified by start & end positions, counting forwards or backwards. Counting forward example
Input: ABCDEF
Start position= 1
End position= 3
Result: ABC

Counting backwards example
Input: ABCDEF
Start position= -3
End position= -1
Result: EF
Tag a value Adds the start and end value to the input value if they are not present in their respective positions. Input: 12345
Start value: <
End value: >

Result: <12345>
Transform text Transform a text value using a variety of operations. Multiple operations can be used in a cumulative manner.
  • Compact spaces - Reduces multiple spaces in input value to single space
  • Double Metaphone - Implement double Metaphone algorithm developed by Lawrence Phillips
  • Double Metaphone (alternate) - Implement alternate double Metaphone algorithm developed by Lawrence Phillips
  • Extract integer - Extract all values that are numbers out of embedded space or letters. Contiguous integers are extracted.
  • Fingerprint - Returns a fingerprint of an input value
  • Format pattern - Returns the format pattern of an input value
  • From hex to text - Converts a hexadecimal input into a string
  • From text to hex - Converts a string input into its hexadecimal value
  • From Unicode to text - Converts Unicode input to a string
  • From text to Unicode - Converts an input string into its Unicode value
  • Improved Metaphone - Implements the improved double Metaphone algorithm
  • Improved Metaphone (alternate) - Implements the improved double Metaphone algorithm for alternate value
  • Lowercase - Converts to lowercase
  • Refined Soundex - Implements refined Soundex algorithm
  • Remove noise - Returns the input value after standardizing it by removing noise from the value. The value is transformed by removing all characters except letters and digits.
  • Remove unprintable characters - Removes unprintable characters in the input value
  • Remove whitespace - Removes whitespaces in an input value
  • Simple format pattern - Returns the simple pattern for a value.Soundex - Soundex is an encoding used to relate similar names, but can also be used as a general-purpose scheme to find words with similar phonemes
  • Standardize - Standardizes a value by removing leading, trailing and compacting spaces. The Double Metaphone and alternate encoding is used for better phonetic representation. It also extracts integers and fingerprints, facilitates HEX, text and Unicode conversion as well as handling accents and simplifying data by applying simple format patterns.
  • Strip accents - Removes diacritics ( ~= accents) from a value
  • Title case - Converts to title case
  • To proper case - Converts to proper case where the first letter is in uppercase and all others are in lower case
  • Uppercase - Converts to uppercase
N/A
Trim Removes all the instances of a single character from the start of the input value (or the end if the trim trailing character option is checked). N/A
Unquote value Removes the quote value from the start and/or end of the input value, but leaves the respective part of the value alone if the quote is not present. Input: ABC12345ABC
Value to remove (case sensitive): ABC

Result: 12345

Functions that typically return a Boolean to indicate validity.

Function Description Example(s)
Contains Checks whether the input value contains the check value, optionally ignoring case. N/A
Ends with Checks whether the input value ends with a given suffix, optionally ignoring case. N/A
Equals Checks the input value for equality with the comparison value(s). It checks for strict equality by default and can be configured to ignore differences in datatype, spaces, non alphanumeric characters and non ASCII characters. N/A
Equals error text Checks whether the input value equals a specified error message. N/A
Equals warning text Checks whether the input value equals a specified warning message. N/A
Greater than Checks whether the input value is greater than to the comparison value, optionally ignoring case. N/A
Greater than or equal Checks whether the input value is greater than or equal to the comparison value, optionally ignoring case. Input: 100
Comparison value: 100

Result: true
In length range Checks whether the input is within the specified start and end length range. Input: Four
Start of length range: 3
End of length range: 5

Result: true
In range Checks whether the input is within the specified start and end range, optionally ignoring case. Numeric example
Input: 100
Start of range: 1
End of range: 100
Result: true

Alphabetic example
Input: b
Start of range: A
End of range: C
Result: false
Ignore case result: true
Is alphabetic Checks that the input value is alphabetic. N/A
Is alphanumeric Checks that the input value is alphanumeric. N/A
Is date Checks whether the value if of date data type. N/A
Is decimal Checks whether the value is a decimal. Note: integers don't pass this test. N/A
Is empty Checks whether the value is empty. An empty value is a:
  • whitespace
  • null
  • empty list
  • zero-length string
  • unprintable character
N/A
Is error Checks whether the value is an error. N/A
Is even Checks whether the value is an even number. N/A
Is false Checks whether the input is a false value. N/A
Is integer Checks whether the value is an Integer. N/A
Is leap year Checks whether the input value is a leap year. N/A
Is negative Checks whether the input value is a negative number. N/A
Is null Checks whether the input is a null value. N/A
Is number Checks whether the input value is a number. N/A
Is odd Checks whether the value is an odd number. N/A
Is positive Checks whether the input value is a positive number. N/A
Is true Checks whether the input is a true value. N/A
Is valid CUSIP Checks whether the input is a valid id defined by the Committee on Uniform Security Identification Procedures (CUSIP). N/A
Is valid ISIN Checks whether the input is a valid International Securities Identification Number (ISIN). N/A
Is valid SEDOL Checks whether the input is a valid Stock Exchange Daily Official List (SEDOL) number. N/A
Is warning Checks whether the input value is a warning. N/A
Less than Checks whether the input value is less than the comparison value, optionally ignoring case. N/A
Less than or equal Checks whether the input value is equal to or less than the comparison value, optionally ignoring case. N/A
Matches expression Checks whether the input matches a regular expression. N/A
Matches format Checks whether the input matches a format pattern. N/A
Multi compare Performs multiple compare operations in one transformation.
Returns true if any of the individual arguments are true.
  • Contains - Checks whether the input value contains the check value
  • Ends with - Checks whether the input value ends with a given suffix
  • Equals (Strict) - Checks the input value for strict equality with the comparison value
  • Equals - Checks the input value for equality with the comparison value(s) based solely on appearance i.e. ignores differences in datatype, non alphanumeric characters, non ASCII characters and spaces.
  • Equals error text - Checks if the input value equals a specific error message
  • Equals warning text - Checks if the input value equals a specific warning message
  • Greater than - Checks if the input value is greater than the comparison value
  • Greater than or equal to - Checks if the input value is greater than or equal to the comparison value
  • Less than - Checks if the input value is less than the comparison value
  • Less than or equal to - Checks if the input value is less than or equal to the comparison value
  • Matches expression - Checks if the input value matches a regular expression
  • Matches format - Checks if the input value matches a format
  • Sounds like - Checks if the input value phonetically matches a comparison value
  • Starts with - Checks if the input value starts with any given prefix
Input: ABC123

Contains: ABC + Ends with: 123
Result: true

Contains: ABC + Starts with: 123
Result: false
Phone numbers match Tests if two numbers represent the same phone number. Input 1: 07771112233
Input 2: +447771112233
Country code: United Kingdom
Result: true

Input 1: 07771112233
Input 2: +447771112233
Country code: Afghanistan
Result: false
Phone numbers match (dynamic) Tests if two numbers represent the same phone number, with country code supplied dynamically. The country code must be in the CLDR two-letter region-code format as described in the CLDR territory information table. Input 1: 07771112233
Input 2: +447771112233
Country code: GB
Result: true

Input 1: 07771112233
Input 2: +447771112233
Country code: AF
Result: false
Sounds like Checks whether the input value phonetically matches the comparison value. N/A
Starts with Checks whether the input value starts with a given prefix, optionally ignoring case. N/A
Validate phone number Validates a phone number using the selected test. N/A
Validate phone number (dynamic) Validates a phone number using the selected test, with country code supplied dynamically. The country code must be in the CLDR two-letter region-code format as described in the CLDR territory information table. N/A

Data Studio allows you to not only create your own Functions (by combining native Functions), but also to categorize them for ease of use and share them with other users. You can also assign variables and use parameters to make your Functions more succinct and easy to maintain/test.

External label specifications

External labels for Functions have to:

  • contain only alphanumeric characters or underscores,
  • be less than 255 characters long and
  • not start with a number or an underscore.

Create a custom Function

To create a Function:

  1. Go to Functions.
  2. Click Create new Function.
  3. Enter a descriptive name.
  4. (Optional) Enter a summary (this will appear in the Function list).
  5. (Optional) Enter a description which could describe what the Function does or when/why it should be used. This is particularly helpful if you plan to share it with other users.
  6. (Optional) Enter an External label to identify this Function from systems external to Data Studio. If you don't enter a value, it will be auto-populated.
  7. (Optional) Select a category for the Function to make it easier to find.
  8. (Optional) To help other users find this Function, enter any aliases (comma separated).
  9. (Optional) Select Data tags to suggest this Function for tagged columns.
  10. (Optional) Include the Function when using Suggest validation or Suggest transformation.
  11. Click Apply to save changes.

Make a Function re-usable

You'll often get to the Function editor from a specific Workflow step (such as Filter, Transform or Validate). While building out the Function, you might decide that the operation (or part of it) would be useful in other steps or even other Workflows. To do that, you have to make it re-usable first.

Turn on Drag select (or Ctrl+click), highlight all of the required Function components (ensure this returns to a single value to be a valid Function) and click Make re-useable. Give your Function a name, description and other details then Apply to create a custom Function.

Using variables

Variables are useful to simplify complex Functions and make them easier to read. They are especially useful when using the same value multiple times in a Function.

To turn part of your Function into a variable, click on a step then Make variable. Give it a name and some description text. If you add a description, the text will appear as a tooltip when hovering over the variable, wherever it's used in the Function..

Once created, it is possible to select this Variable as an Input value anywhere else in the Function.

As well as readability, variables appear as columns in the Preview grid. This allows you to see the value of the input mid-way through the Function, next to the final result, so you could introduce a variable at each stage of a complex Transformation to ensure the results are correct at each stage.

Testing your Function with parameters

To test that a Function works in different scenarios, you can specify test data for it by clicking Parameters while in edit mode.

A parameter can be a column of values from an existing Dataset/View, or you can define your own comma-separated list of test values. The values you enter will be treated as alphanumeric unless a datatype is specified:

  • Numeric value - prefix with {n}, e.g.: {n}1234
  • Boolean value - prefix with {b}, e.g.: {b}true
  • Date value - prefix with {d}, e.g.: {d}1990-01-01 or {d:yyyy-MM-dd}1990-02-03
  • Use double quotes " " if a test value contains a comma, e.g.: AB-123,{n}23.53,"Test, value"

Each parameter will appear as a column in the Preview grid, making it easy to check whether the Function is returning the expected result.

Function categories

Function categories help you organize your Functions and make them easier to locate in the Function editor. All native Functions are already in one of the existing Function categories.

Custom Functions can be added to an existing category, or new categories can be created (in Functions > Manage categories) by users who have the "Create and Edit Function Categories" permission in their Role.

Once created, Function categories are available across all Spaces.

Function versions

You can have more than one version of a Function. For example, you may want to improve a previously published Function. To create a new version in draft status, simply edit the Function.

To see the version history, go to Functions, select the required Function and click Options > Versions. To revert to a previous version, go to Functions and click Options > Revert to.

Share Functions

By default, a Function you create will be restricted to the Space it has been created in. You can share Functions with everyone by making them available to all Spaces. Only published Functions can be shared.

To share a Function:

  1. Go to Functions and select the Function you want to share.
  2. Click Options > Sharing Options. If this option cannot be selected, you may not have a published version of the Function.
  3. Select the Scope of the sharing. Use Shared with all Spaces to make this Function available to all Spaces.
  4. Click Apply to save changes.

You can use a scripting language to create new and manage existing Functions. Changes made to the script are then reflected in the Function canvas and vice versa, so you can use both interchangeably depending on the task.
Click Edit script to open the script editor and make the required changes.

To open a read-only version, click View script.

You can create validation rules using the same scripting functionality in the validation rules script editor.

A script is ordered with parameters first, then variables, and finally a Function. Each parameter and variable must end with a semicolon. For example:

param Account Status;
param Payment Rating;
var RatingE = Contains([p:Payment Rating], 'E');
Or(
    Equals([p:Account Status], 'OPEN'),
    [v:RatingE]
)

Functions are selected using their external label, followed by brackets. For native Functions, this is typically the Function name without spaces. Parameters are created by using param = parameter; and variables are created assigning a Function var variableName = Function();

Function arguments have different syntax dependent on their type:

  • Text values or strings should be in 'single quotes'.
  • Numbers and booleans do not need quotes: 6, 7, true, false, null, ignore.
  • Dates and times should be @(1999-12-31) or @(1999-12-31T11:59:59).
  • Special characters should be in back quotes `Ascii 0`.
  • Square brackets denote a column, parameter, variable or datasource: [c: column name], [p: parameter], [v: variable], [s: datasource], [sc: datasource column].
  • Use Ctrl+Space after entering [c: or [p: or [v: to show available columns, parameters or variables.
  • Curly brackets denote a multi argument within a Function, typically used when there is more than one comparison value. For example: Equals([c:Discount Code], {'A','D','P','S'}).

Function script editor tips

  • Use Ctrl+Space to show available Functions or to suggest a Function according to what you have already typed.
  • Hover over a Function name with the cursor to show the arguments required for that Function.
  • Use Ctrl+Enter to validate and reformat the Function script.
  • Syntax errors will typically correspond to a line (listed vertically) and a column (horizontal character). To help with identifying the position of errors, try splitting arguments onto different lines and use Ctrl+Enter to validate the Function.