| Available in: Intelligence Reports Advanced |
| Function Name | Syntax | Description |
|---|---|---|
| CHAR | CHAR(number) | Returns a character specified by a number. |
| COALESCE | COALESCE([value1, value2,...]) | Returns the first value from the list that isn’t empty. |
| CODE | CODE(text) | Returns a numeric code for the first character in a text string. |
| COUNT_CHAR | COUNT_CHAR(text, charToCount) |
Counts how many times a specific character appears in a text. |
| EXTRACT | EXTRACT(text, delimiter, position) | Extracts from the given text the substring, at the given position, after splitting by the given delimiter. For example, EXTRACT(‘a,b,c’,’_’,1) returns ‘b’. |
| FIND | FIND(findText, withinText) | Returns the starting position of findText withinText. Returns -1 if withinText doesn’t contain findText. |
| FUZZYMATCH | FUZZYMATCH(valueToMatch, array, threshold) | Searches for a string in an array of values that potentially match using duplicate-detection algorithms that calculate the similarity of two streams of data. Returns the matched string from the array if there’s a match or, if no matches are found, returns an empty string. For example, FUZZYMATCH("ABC Company",{"ABC Company Inc","Apple Inc","IBM Company"},0.8) returns "ABC Company Inc". The threshold parameter must be a value between 0 and 1. A threshold of 1 means an exact match. |
| GET_EXCHANGE_RATE | GET_EXCHANGE_RATE(date, fromISO, toISO) |
Get the exchange rate for the given currencies and date. The ‘date’ variable in the formula can be a mapped date field or a date function For example:
where [Day] is a mapped date field
where DATE('YYYY','MM','DD') is a date function.
where YESTERDAY() is a date function. |
| INDEXOF | INDEXOF(array, findText) |
Searches the array for the specified item, and returns its position. Returns -1 if the item isn’t found. If the item is present more than once, the INDEXOF function returns the position of the first occurrence. For example, INDEXOF(csv['row'], 'Campaign ID') == 0 returns true if 'Campaign ID' was found in the first cell. Note: The first item has position 0, the second item has position 1, and so on. |
| ISEMPTY | ISEMPTY(value) | Returns if the value is empty or not. |
| LEFT | LEFT(text, numOfChars) | Returns the requested number of characters from the left. |
| LEN | LEN(text) | Returns the number of characters in the given text |
| LOWER | LOWER(text) | Converts all upper-case letters in a string to lower-case letters. |
| MATCH | MATCH(valueToMatch, array) | Searches for a specified item in an array of values, and then returns a boolean result. For example, MATCH(1,{1,2,3}) returns true because the value '1' exists in the array. |
| PROPER | PROPER(text) | Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. |
| REPLACE | REPLACE(SourceStr, pattern, targetStr) | Replaces every match of a pattern (a regex expression) in sourceStr to targetStr. For example, REPLACE('All-The-Best', '-', ' ') returns 'All The Best'. In another example, REPLACE('Let It Be','\\s','-') returns 'Let-It-Be'. |
| RIGHT | RIGHT(text, numOfChars) | Returns the requested number of characters from the right. |
| SELECT | SELECT(sourceStr, pattern) | Selects the first match of the pattern (a regex expression) in the sourceStr. For example, SELECT('Hello B wow E Goodbye', 'B(.*?)E') returns 'B wow E'. |
| SEMTIMEN | SENTIMEN(text) | Extracts a positive, neutral, or negative sentiment from the given text. |
| SPLIT | SPLIT(text, delimiter) | Splits a text string into an array by using a delimiter character. It returns a zero-based, one-dimensional array holding the parts of the original text string. For example, SPLIT('a,b,c', ',') returns an array with three items. |
| SUBSTITUTE | SUBSTITUTE(text,oldText,newText) | Substitutes newText for oldText in a text string. Use SUBSTITUTE when you want to replace specific text in a text string. |
| SUBSTRING | SUBSTRING(text,beginIndex,endIndex) | Returns the substring of the text beginning at beginIndex (inclusive) and ending at endIndex (exclusive). |
| TRIM | TRIM(text) | Removes all spaces from the text except for single spaces between words. |
| UPPER | UPPER(text) | Converts all lowercase letters in a text string to uppercase. |
| URL_DECODE | URL_DECODE(text) | Decodes an application/x-www-form-urlencoded string using a UTF-8 encoding scheme. |
| URL_ENCODE | URL_ENCODE(text) | Translates a text into an application/x-www-form-urlencoded format using a UTF-8 encoding scheme. |
| VALUE_FROM_JSON | VALUE_FROM_JSON(JSON string, key) | Returns the value for a given key in a JSON string. If the key doesn’t exist or the JSON is empty, an empty string is returned. |
| Function Name | Syntax | Description |
|---|---|---|
| CONTEXT_DIMENSION_CONTAINS | CONTEXT_DIMENSIONS_CONTAIN(dimension) |
Returns a 'true' value if the given dimension is included in the query. |
| CONTEXT_DIMENSION_EQUALS | CONTEXT_DIMENSIONS_EQUALS(dimension) | Returns a 'true' value if the given dimensions are exactly the context dimensions. Enter all the relevant dimensions, but the order of appearance isn’t important. |
| FilterEndDate | FilterEndDate() | Returns the end date of the query filter. |
| FilterStartDate | FilterStartDate() | Returns the start date of the query filter. |
| GET_ENV | GET_ENV(variableName) | Returns the value for the given environment variable. Possible values for variableName are: WORKSPACE_ID |
| QUERY_DIMENSIONS_CONTAIN | QUERY_DIMENSIONS_CONTAIN(dimension) | Returns a 'true' value if the given dimension is one of the query dimensions. |
| QUERY_DIMENSIONS_EQUAL | QUERY_DIMENSIONS_EQUAL([dimensions]) | Returns a 'true' value if the given dimensions are exactly the query dimensions. Enter all the relevant dimensions, but the order of appearance isn’t important. |