Data Quality user documentation

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 Filter, Split, Transform and Validate steps. To see all the Functions available to you, go to the Functions page and select Type > All Functions.

Native Functions
Custom Functions

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 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.	N/A
Compare date/time	Compares two date/time values, returning the difference between them.	N/A
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 as follows (note that casing is important): G: Era designator (AD) y: Year (1996, 96) Y: Week year (2009, 09) M: Month in year (July, Jul, 07) w: Week in year W: Week in month D: Day in year d: Day in month F: Day of week in month E: Day name in week (Tuesday, Tue) 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 determines the exact presentation. For example: y will display "2020", yy "20". M will display "7", MM "07", MMM "Jul", and MMMM "July". E will display "Tue", EEEE will display "Tuesday". Text can be added using single quotes ('): yyyy-MM-dd'T'HH:mm:ss.SSS will display "2020-11-10T13:20:19.124". The only separators allowed are punctuation (,.-; etc.) or spaces. After you format the date the datatype changes from Date to Alphanumeric.	N/A
Get age	Returns the number of years between the supplied date and the current date.	N/A
Get century	Extracts the century from the supplied date.	N/A
Get day of week	Extracts the day of the week (integer) from the supplied date.	N/A
Get day of week name	Extracts the name of the day from the supplied date.	N/A
Get days	Extracts the day from the supplied date.	N/A
Get hours	Extracts the hour from the supplied date.	N/A
Get millis	Extracts the millisecond from the supplied date.	N/A
Get minutes	Extracts the minute from the supplied date.	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 from the supplied date.	Input: 01/01/22 12:31 Result: 1
Get seconds	Extracts the second from the supplied date.	N/A
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.	N/A
Get years	Extracts the year from the supplied date.	N/A

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. Both Standardized and Standardized: no digits also change the meaning of whitespace when splitting a sentence up into words (i.e. they cause the sentence to also be split on punctuation). 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. Both Standardized and Standardized: no digits also change the meaning of whitespace when splitting a sentence up into words (i.e. they cause the sentence to also be split on punctuation). 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. Both Standardized and Standardized: no digits also change the meaning of whitespace when splitting a sentence up into words (i.e. they cause the sentence to also be split on punctuation). 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. Both Standardized and Standardized: no digits also change the meaning of whitespace when splitting a sentence up into words (i.e. they cause the sentence to also be split on punctuation). 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

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 format 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, embedding & trailing spaces, converting whitespace and punctuations to a standard space, except for quote & period characters which are removed. Digits are preserved, letters are preserved as they are lowercase, Unicode normalized version. This Function is used for comparing values in a domain. 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), based solely on appearance which is then standardized, i.e. ignoring any differences in data type and non-alphanumeric characters.	N/A
Equals (Strict)	Checks the input value for strict equality with the comparison value (i.e. the data type has to also match).	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 - Checks the input value for equality with the comparison value Equals (standardized) - Checks the input value for equality with the comparison value(s) based solely on appearance i.e. ignores differences in datatype. 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.

Functions have to have unique external labels across all Spaces within an Environment.

To create a Function:

Go to Functions.
Click Create new Function.
Enter a descriptive name.
(Optional) Enter a summary (this will appear in the Function list).
(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.
(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.
(Optional) Select a category for the Function to make it easier to find.
(Optional) To help other users find this Function, enter any aliases (comma separated).
(Optional) Select Data tags to suggest this Function for tagged columns.
(Optional) Include the Function when using Suggest validation or Suggest transformation.
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. Once defined, variables appear as columns in the Preview grid allowing you to see the input, variable value and result in the same row.

For example, you have multiple steps that Trim, Replace and Concatenate an input value. Instead of linking this to the rest of the Function, you can click the final step and Make variable. This will let you give a descriptive name that can then be used elsewhere in the Function. Variables are especially useful when using the input multiple times in a Function.

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:

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

Previous: Create a Workflow

Next: Manage versions

Aperture Data Studio v2

Get started

Next topic:
Definition of SCV
Previous topic:
Technical recommendations

Create Functions