Function |
Description |
~= |
Can be part of an 'If' or 'Case' statement. Returns true if the Arg2 regular expression string matches the Arg1 string. Examples: If( "aab" ~= "a+" , "true", "false" ) Case when "aba" ~= ".a+$" then "true" else "false" end |
Concatenate(Arg1, ...) |
Concatenates (appends) all the arguments into a string. If one argument is given, then the result is the concatenation of all rows. If more than one argument is given, then each row is concatenated. The arguments can be of any type, but are converted to strings. The result is of type string. Null arguments are ignored. Examples: |
Find(Arg1, Arg2, Arg3) |
Returns the index of the occurrence of the string Arg1 in Arg2. Arg3 optionally indicates which match to return. If no third argument is given then the first match is returned. If no match is found, 0 is returned. The search is case-sensitive. The first two arguments are of type string and the third argument and the result are of type integer. If Arg1 is the empty string, 0 is returned. Examples: If(Find("Pri 1", [Col1])>0, "Important", "Not important") Find("a", "ababab") → 1 Find("a", "ababab", 2) → 3 Find("a", "ababab", 10) → 0 |
Left(Arg1, Arg2) |
Returns the first Arg2 characters of the string Arg1. Arg1 and the result are of type string. Arg2 is of type real, but only the integer part is used. If Arg2 > the length of Arg1, the whole string is returned. If Arg2 is negative, then the Arg2 number of characters will be removed from the right-hand side of the Arg1 string. Examples: Left("Daddy", 3.99) →
"Dad" |
Len(Arg1) |
Returns the length of Arg1. Arg1 is of type string and the result is of type integer. Examples: |
Lower(Arg1) |
Returns Arg1 converted to lowercase. Arg1 and the result are of type string. |
Mid(Arg1, Arg2, Arg3) |
Returns the substring of Arg1 starting at index Arg2 with a length of Arg3 characters. Arg1 and the result are of type string. Arg2 and Arg3 are of type real, but only the integer part is used. If Arg2 > Len(Arg1), an empty string is returned. Else, if Arg2+Arg3 > Len(Arg1), Arg3 is adjusted to 1+Len(Arg1)-Arg2. If either of Arg2 or Arg3 is negative or if Arg2 is zero, an error is returned. Examples: Mid("Daddy", 2, -1) → (Empty) |
MostCommon(Arg1) |
Returns the most common value of the specified column. If several values are equally common, the first one will be used. Example: MostCommon([Column]) |
Replaces all substring codes with decoded characters. Column names in Spotfire are stored as UTF-16 encoded strings, If you write a data function that uses 8-bit ASCII characters matching [.0-9a-zA-Z] or ASCII strings enclosed in grave accents, then the column names must be encoded. This function can be used to decode results that have not been automatically decoded. Example: NameDecode("Column %02D") |
|
Encodes characters in the string so that the string only contains characters matching the regular expression [.0-9a-zA-Z]. Column names in Spotfire are stored as UTF-16 encoded strings. If you have a data function that uses 8-bit ASCII characters matching [.0-9a-zA-Z], then the column names must be encoded. If you need to provide column name input by some other means (for example, in a document property) you might need to use this function to encode the column names before applying the data function. Example: NameEncode("Column £") |
|
Repeat |
Repeats a string a specified number of times. Example: Repeat("Hello", 2) → "HelloHello" |
Right(Arg1, Arg2) |
Returns the last Arg2 characters of the string Arg1. Arg1 and the result are of type string. Arg2 is of type real, but only the integer part is used. If Arg2 > the length of Arg1, the whole string is returned. If Arg2 is negative, then the Arg2 number of characters will be removed from the left-hand side of the Arg1 string. Examples: Right("Daddy", 3.99) →
"ddy" |
RXExtract(Arg1, Arg2, Arg3) |
Returns the part of a string (Arg1) that matches a regular expression (Arg2). Arg3 determines which match to use when there are multiple matches. Examples: RXExtract([Column], "l+", 1) RXExtract("Parallel", "l+", 1) → "ll" RXExtract("Parallel", "l+", 2) → "l" |
RXReplace(Arg1, Arg2, Arg3, Arg4)
|
Replaces a substring according to a regular expression. Search for the Arg2 regular expression in Arg1 and replace it with Arg3. Arg4 specifies the options for the replacement: "g" specifies that if Arg2 matches more than once then all matches should be substituted. "i" specifies that the comparison should be case insensitive. "s", for single-line mode, specifies that the dot (.) matches every character (instead of every character except newline). Some characters, like for instance the backslash character "\", and parentheses, "(" and ")", need to be escaped to work when using calculated columns. Because parenthesis are used for grouping in regular expressions, both "(" and ")" need to be escaped if the ( or ) sign should be matched, and when the character is used in a regular expression double backslashes are needed in order to escape both the Spotfire string and the regular expression. See literature about regular expression language elements, e.g., on MSDN, for more information. Example: RXReplace("Hello", "L+", "LL", "i") → "HeLLo" RXReplace("3 Minor", "(\\d).*", "$1", "") → 3 RXReplace([Column with values within parenteses], "\\(89\\)", "", "") RXReplace("change\\slashdirection","\\\\","/","") → change/slashdirection (In the last example, the backslash needs to be escaped twice; once for the Spotfire string and once for the regular expression.) Note that there are some differences in the implementation of RXReplace in the versions of Spotfire before and after version 5.0. If you are upgrading to a newer version, you may need to validate your regular expressions. For example, some regular expressions that were not well formed may now be regarded as invalid and no result is shown. |
Split(Arg1, Arg2, Arg3) |
Splits the values in Arg1 into a number of substrings, using Arg2 as the separator. Arg3 specifies which of the values should be returned as a new column. Only one column is returned. Use a negative Arg3 to specify that the split should be done from the end of the string toward the beginning, instead of from the beginning toward the end. For example, if a column called "X" containing the value "a.b" is split into two parts using the separator ".", then Split([X],".",1) will return "a" and Split([X],".",2) will return "b". Examples: Split([Column]," ",2) Split([Column]," ",-2) Split([Column]," / ",3) |
Substitute(Arg1, Arg2, Arg3)
|
Replaces all occurrences of Arg2 in Arg1 with Arg3. The search is case sensitive. Example: Substitute("Test","t","ting") → "Testing" |
Substring(Arg1, Arg2, Arg3) |
Returns the substring of Arg1 starting at index Arg2 with a length of Arg3 characters. Arg1 and the result are of type string. Arg2 and Arg3 are of type real, but only the integer part is used. If Arg2 > Len(Arg1), an empty string is returned. Else, if Arg2+Arg3 > Len(Arg1), Arg3 is adjusted to 1+Len(Arg1)-Arg2. If either of Arg2 or Arg3 is negative or if Arg2 is zero, an error is returned. Examples: Substring("Daddy", 2, -1) → (Empty) |
Trim(Arg1) |
Removes whitespace characters from the beginning and end of a string. Example: Trim(" Example ") →"Example" |
UniqueConcatenate(Arg1)
|
Concatenates the unique values converted to strings. The values are ordered according the comparator. Example: UniqueConcatenate([Column]) |
Upper(Arg1) |
Returns Arg1 converted to uppercase. Arg1 and the result are of type string. Example: Upper("hello") →"HELLO" |
See also: