A Function is a piece of processing logic that transforms, filters or validates 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 check whether input data is an integer; or it can find, analyze and then remove data matching specified criteria.
Data Studio comes with a variety of default Functions that can be used as standalone Functions, or as the building blocks for more complex custom Functions. You can also create, save and share your own custom Functions.
Function | Category | Description |
---|---|---|
Add period | Date and time | Add a period to a date/time expression. |
After | Text transform | Extracts, from an input value, everything that follows a search value. For example, given the input value ABC123, and a search value of ABC, this function will return 123. |
And | Logical operators | The logical operator "and". If all of the inputs evaluate to true, it returns true. |
Auto convert | Conversion | Automatically infers the data type. |
Before | Text transform | Extracts, from an input value, everything that precedes a search value. For example, given the input value 123ABC, and a search value of ABC, this function will return 123. |
Calculate | Math operations | Evaluate a simple mathematical calculation using +, -, /, * and %.
|
Chunk | Text transform | Splits the input value into variable length chunks using the supplied lengths, returning a list of split values. |
Combine lists | List transformation | Joins two lists of values in different ways. Possible operations are:
|
Common in list | List transformation | Analyzes a list and return two values. The first is most or least common in the list, and second is how often it occurred.
|
Common prefix | Text transform | Compares all supplied values and returns the prefix that is common to all of them. |
Compare date/time | Date and time | Compares two date/time values, returning the difference in the specified time period units. |
Concatenate | Text transform | Concatenates two variables to form a new single variable. |
Constant value | Other | Returns the specified value. |
Contains | Validation | Checks whether the input value contains the check value, optionally ignoring case |
Contains match | Lookup | Returns true if any element of a list is contained in a lookup column. The following match types are available:
|
Convert to date | Date and time | Attempts to convert an alphanumeric value to a date value. For example:
The Function includes settings to define how the year is resolved if only a two-digit year is provided, for example 25.12.49:
|
Create date/time stamp | Date and time | Creates a time stamp value with a supplied year, month, day, hours, minutes and seconds. For example: Input year: 2009 Input month: 12 Input day: 25 Input hours: 8 Input minutes: 12 Input seconds: 25 Returned value: 25-Dec-2009 08:12:25 |
Create list | Text transform | Creates a value list from any number of values. |
Current row | Dynamic reference | Returns the row ID, starting at 1, for the current row (i.e. the row being displayed). |
Current timestamp (dynamic) | Dynamic reference | Returns the current timestamp. |
Datatype | Conversion | Returns the data type of the input value. |
Decode geohash coordinates | Encoding | Decodes a geohash to its latitude or longitude component |
Delimited substring | Text transform | Returns the substring of a value using a start delimiter or an index position and either an end delimiter or a return value length. For example: Input value: ABC,DEF,GHI Delimiter: , Length: 5 Returned value: DEF,G |
Difference | Text transform | Returns the portion that is a difference between two values. |
Ends with | Validation | Checks whether the input value ends with a given suffix, optionally ignoring case. |
Equals | Validation | 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. |
Equals (Strict) | Validation | Checks the input value for strict equality with the comparison value (i.e. the data type has to also match). |
Equals error text | Validation | Checks whether the input value equals a specified error message. |
Equals warning text | Validation | Checks whether the input value equals a specified warning message. |
Error message | Information | Returns an error message, setting the expression into error and displaying the result in red. |
Escape | Encoding | Escapes or un-escapes a string based on a given escaping style. |
Example phone number | Cleansing | Generate an example phone number for a specified region in various formats. |
Expand list | List transformation | Extract all members of a list and separate them with a specified delimiter |
Extract from list | List transformation | Parses a value that is a list of comma-separated values, allowing any value to be extracted by index position in the list. |
Extract matches as list | Lookup | Extracts elements from the input that are contained in a lookup column and outputs them as a list. The following match types are available:
|
First non null | List transformation | Given a variable number of input values, returns the first value that is not null (empty). |
Format date | Date and time | 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):
|
Format phone number | Cleansing | Format a phone number or extract specific attributes. |
Format phone number (dynamic) | Cleansing | 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. |
Geohash coordinates | Encoding | Converts latitude and longitude to a geohash. |
Get age | Date and time | Returns the number of years between the supplied date and the current date. |
Get cell | Dynamic reference | Returns a value from a cell at a given row/column position from the source of current drilldown. |
Get century | Date and time | Extract the century from the supplied date. |
Get day of week | Date and time | Extracts the day of the week (integer) from the supplied date. |
Get day of week name | Date and time | Extracts the name of the day from the supplied date. |
Get days | Date and time | Extracts the day from the supplied date. |
Get hours | Date and time | Extracts the hour from the supplied date. |
Get millis | Date and time | Extracts the millisecond from the supplied date. |
Get minutes | Date and time | Extracts the minute from the supplied date. |
Get month name | Date and time | Extracts the name of the month from the supplied date. |
Get months | Date and time | Extracts the month from the supplied date. |
Get seconds | Date and time | Extracts the second from the supplied date. |
Get weeks | Date and time | 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. |
Get years | Date and time | Extracts the year from the supplied date. |
Greater than | Validation | Checks whether the input value is greater than to the comparison value, optionally ignoring case. |
Greater than or equal | Validation | Checks whether the input value is greater than or equal to the comparison value, optionally ignoring case. |
Hash code | Encoding | Returns a generated hash code for the input value based on the specified algorithm. |
If then else | Logical operators | Checks the condition field for the value of true. If it's true, returns the condition met field, otherwise else. Can also have multiple condition fields to be evaluated if the precursory condition is not met. |
In length range | Checks whether the input is within the specified start and end length range. | |
In range | Checks whether the input is within the specified start and end range, optionally ignoring case. | |
Index of difference | Returns the position where two values begin to differ, starting from 1 or 0 if there is no difference. | |
Initials | Returns the initials by splitting words from the input value within optional list of delimiters (default is white space). | |
Insert | Insert a value into the input value at a given position. | |
Is alphabetic | Validation | Checks that the input value is alphabetic. |
Is alphanumeric | Validation | Checks that the input value is alphanumeric. |
Is date | Validation | Checks whether the value if of date data type. |
Is decimal | Validation | Checks whether the value is a decimal. Note: integers don't pass this test. |
Is empty | Validation | Checks whether the value is empty. An empty value is a:
|
Is error | Validation | Checks whether the value is an error. |
Is even | Validation | Checks whether the value is an even number. |
Is false | Validation | Checks whether the input is a false value. |
Is integer | Validation | Checks whether the value is an Integer. |
Is leap year | Validation | Checks whether the input value is a leap year. |
Is negative | Validation | Checks whether the input value is a negative number. |
Is null | Validation | Checks whether the input is a null value. |
Is number | Validation | Checks whether the input value is a number. |
Is odd | Validation | Checks whether the value is an odd number. |
Is positive | Validation | Checks whether the input value is a positive number. |
Is true | Validation | Checks whether the input is a true value. |
Is valid CUSIP | Validation | Checks whether the input is a valid id defined by the Committee on Uniform Security Identification Procedures (CUSIP). |
Is valid ISIN | Validation | Checks whether the input is a valid International Securities IdentificationNumber (ISIN). |
Is valid SEDOL | Validation | Checks whether the input is a valid Stock Exchange Daily Official List (SEDOL) number. |
Is warning | Validation | Checks whether the input value is a warning. |
Jaro edit measure | Cleansing | 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. |
Jaro-Winkler edit measure | Cleansing | 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. |
Length | Returns the number of characters in the given string. | |
Less than | Validation | Checks whether the input value is less than the comparison value, optionally ignoring case. |
Less than or equal | Validation | Checks whether the input value is equal to or less than the comparison value, optionally ignoring case. |
Levenshtein distance | Cleansing | 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. For example, 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 | Cleansing | Calculates the Levenshtein edit distance between the input value and the compare value returning a percentage similarity between the two values. |
List | List transformation | Transform a list of values using a variety of operations. Multiple operations can be used in a cumulative manner.
|
List frequency | List transformation | Returns unique values in the list interleaved with their count (frequency). |
Matches expression | Validation | Checks whether the input matches a regular expression. |
Matches format | Validation | Checks whether the input matches a format pattern. |
Multi compare | Validation | Performs multiple compare operations in one transformation. Returns true if any of the individual arguments are true.
|
Not | Logical operators | Turns the input value from true to false or vice versa. |
Or | Logical operators | The logical operator or. If any one of the inputs evaluates to true it will return true. |
Pad | Text transform | Pads any value with a chosen character to achieve a given overall length, with the option of putting the padding before or after the given value. |
Parse | Cleansing | Parses a value, extracting other values which match one of the supplied format patterns or regular expressions. This can be used to extract values that look like an expected type of value from a free-text value. For example, an embedded postal code in an address string. The result can be deduplicated, comma separated quoted list of values for each result found. Multiple patterns can be searched at the same time and they are searched in sequence.
|
Partition first | Text transform | Returns the list of 3 values ['before', 'fragment', 'after'], where 'before' is the part of input value before the 1st occurrence of the fragment and 'after' is what's left. A fragment is a regular expression. |
Partition last | Text transform | Returns the list of 3 values ['before', 'fragment', 'after'], where 'before' is the part of input value before the 1st occurrence of the fragment and 'after' is what's left. A fragment is a regular expression. |
Phone number match code | Cleansing | Returns a match code for comparison of two phone numbers: EXACT_MATCH, NSN_MATCH (including extensions), SHORT_NSN_MATCH (ignoring extensions) or NO_MATCH. |
Phone number match code (dynamic) | Cleansing | 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 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. |
Phone numbers match | Validation | Tests if two numbers represent the same phone number. |
Phone numbers match (dynamic) | Validation | 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. |
Position | Text transform | Returns the first index position, starting from 1, of a search value within the input value. |
Power of n | Math operations | Returns the nth power of the input value. |
Regular expression replace | Text transform | Replaces all the instances of the search value with the replacement value. Example: Using the input value 'ABC::DEF:::::::::GHI', the search value ':+'and the replacement value ';' will return 'ABC;DEF;GHI' This differs from the 'replace' function as the search value can be a regular function as opposed to a constant value. |
Remove matches | Lookup | Removes elements from the input that are contained in a lookup column. The following match types are available:
|
Remove noise | Cleansing | 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:
|
Repeat | Text transform | Returns an input value repeated a defined number of times. Example: Input Value: ABC Repeat: 3 Return Value: ABCABCABC |
Replace | Text transform | Replaces all the instances of the search value with the replacement value. Example: Using the input value 'ABC:DEF:GHI', the search value ':' and the replacement value ';' will return 'ABC;DEF;GHI' |
Replace first | Text transform | Replaces first the instances of the search value with the replacement value. Example: Using the input value 'ABC:DEF:GHI', the search value ':' and the replacement value ';' will yield the result 'ABC;DEF:GHI'. |
Replace matches | Lookup | Replaces elements from a list with matching elements from a lookup table. The following match types are available:
|
Round number | Number | Sets the number of decimal points after a decimal value. |
Row count | Other | Returns the total number of rows. |
Sounds like | Validation | Checks whether the input value phonetically matches the comparison value. |
Split | Text transform | Splits a value using another value in the field separator. |
Starts with | Validation | Checks whether the input value starts with a given prefix, optionally ignoring case. |
Strings between | Text transform | Searches a value for substrings delimited by a start and end tag, returning all matching substrings in a list. |
Strip substring | Text transform | Strips the supplied substring value from the given input value if present. Example: If input value is 'abcdefghi' and search value is 'def', the result would be 'abcghi'. |
Substring | Text transform | Returns substring of a given value specified by giving start and end positions. |
Tag a value | Text transform | Adds the start and end value to the input value if they are not present in their respective positions. Example: Input value: 12345 Start Value: < End Value: > Return value: <12345> |
To alphanumeric | Conversion | Converts any value to an alphanumeric value. |
To date | Conversion | Converts any value to a date value. For example: Input value: 25.12.2009 Returned value: 25-Dec-2009 |
To decimal | Conversion | Converts any value to a decimal value. |
To integer | Conversion | Converts any value to an integer value. For example: Input value: 12.3 Returned value: 12 |
To null | Conversion | Converts any blank (spaces) values to null ones (empty). |
To spaces | Conversion | Converts any null (empty) values to blank ones (spaces). |
To time | Conversion | Converts any value to a time period. For example: Input value: 00-00-23 Returned value: 00:00:23 |
Transform number | Transforms a number using any of a wide selection of mathematical and scientific conversions
|
|
Transform text | Transform a text value using a variety of operations. Multiple operations can be used in a cumulative manner.
|
|
Trim | Text transform | 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). |
Unquote value | Text transform | 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. Example: Input value: ABC12345ABC Value to remove (case sensitive): ABC Returns value: 12345 |
Validate phone number | Validation | Validates a phone number using the selected test. |
Validate phone number (dynamic) | Validation | 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. |
Warning message | Information | Returns a warning message and displays the result in yellow. |
Data Studio allows you to not only create your own Functions but also to categorize them for ease of use and share them with other users. What's more, you can assign variables and use parameters to make your Functions more succinct and easy to maintain and test.
To create a Function:
You can make a part or the whole Function re-usable: select all the required Function parts and click Make reusable. You can also do this while editing a Workflow or a View.
You can re-use a value in a Function by turning the output of one Function step into a variable to be used as the input to one or more other Function steps within the same overall Function: select a step in the Function and click Make variable. This allows you to create a variable representing the value at that point, and re-use the variable within the scope of that Function.
Parameters allow you to test your Function without having to access a Dataset or a Workflow. When editing a Function, click Parameters to add parameters to the Function. To test the Function works, you can specify test data from an existing Dataset or View, or enter your own comma-separate list of test values for each parameter.
Use double quotes to supply Parameter test values that contain a comma. All values will be treated as a Alphanumeric unless a specific datatype is specified as follows:
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.
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:
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 native 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.