Conversion functions
Available conversion functions are listed.
| Function | Description |
|---|---|
| Base64Decode(Arg1) | Decodes a base64 string into a binary large object so it can be viewed as an image in Spotfire.
Example: Base64Decode([Column]) |
| Base64Encode(Arg1) | Encodes a binary large object as a base64 string. This might be of interest if data from Spotfire is to be exported to other applications.
Example: Base64Encode([Column]) |
| Boolean(Arg1) | Converts the column or value to a
Boolean.
Example: Boolean([Column]) |
| Cast(Arg1 as type) | Casts any expression to any type (except Null/Undefined).
Invalid values are propagated. Casting performed for different types of input and output types results in different outputs. See Cast method for more information.
Example: Cast([IntegerColumn] as Currency) |
| Currency(Arg1) | Converts the column or value to a
Currency.
Example: Currency([Column]) |
| Date(Arg1, ..., Arg3) | Converts the column or values to a
Date. If a single argument is used,
Arg1 can be of type
String or
DateTime. If a
String is specified, the date must be written in a format that
Spotfire can recognize. Additionally, all parts of the date (year, month and day) must be present. See examples below. If a
DateTime is specified, the time part is removed.
If three arguments are given, they must be Integer values. The first argument is the year, the second is the month and the third is the day of the month. If a single Integer argument is given, the Date function will interpret the Integer as ticks. Ticks are counted with the unit 100 nanoseconds, starting from the date 01-01-01 (January first, year one). See also Date and time functions and Cast method.
Examples: Date("2003-03-21") → 3/21/2003 Date("3/21/03") → 3/21/2003 Date("10") → (Empty) Date(null) → (Empty) Date("2003-03-21 11:37:00") → 3/21/2003 Date(2003,03, 21) → 3/21/2003 Date(605000000000000000l) → 3/4/1918 (The output formats available are dependent on your current locale.) |
| DateTime(Arg1, Arg2,..., Arg7) | Converts the column or values to a
DateTime. If a single argument is used,
Arg1 can be of type
String or
Date. If a
String is specified, the date must be written in a format that
Spotfire can recognize. Additionally, at least all parts of the date (year, month and day) must be present. If a
Date is specified, the time part is set to 00:00:00 (12:00:00 AM).
If seven arguments are given, they must be Integer values. The first argument is the year, the second is the month, the third is the day of the month, the fourth is the hour, the fifth is the minute, the sixth is the second and the seventh argument is the millisecond. If a single Integer argument is given, the DateTime function will interpret the integer as ticks. Ticks are counted with the unit 100 nanoseconds, starting from the date 01-01-01 (January first, year one). See also Date and time functions and Cast method.
Examples: DateTime("2003-03-21 11:37:00") → 3/21/2003 11:37:00 AM DateTime("10") → (Empty) DateTime(null) → (Empty) DateTime("2003-03-21") → 2003-03-21 00:00:00 DateTime(2003, 03, 21, 11, 37, 00) → 2003-03-21 11:37:00 DateTime(605000000000000000l) → 3/4/1918 11:33:20 AM (The output formats available depend on your current locale.) |
| FirstValidAfter(Arg1) |
Substitutes null values in a column with the first valid value after the missing value.
Example: FirstValidAfter([Column]) |
| FromEpochMilliseconds(Arg1) |
Converts the column or value to a datetime, where numbers are interpreted as the number of milliseconds that have passed since 1970-01-01.
Example: FromEpochMilliseconds(231312) |
| FromEpochSeconds(Arg1) |
Converts the column or value to a datetime, where numbers are interpreted as the number of seconds that have passed since 1970-01-01.
Example: FromEpochSeconds(231312) |
| Integer(Arg1) | Converts the column or value to an
Integer number. If the conversion fails, an error is returned.
Arg1 can be of type
Integer,
Real or
String, and the result is of type
Integer.
Real numbers are truncated, i.e., only the integer part is used.
Examples: Integer("-123") → -123 Integer(-2.99) → -2 Integer("0%") → (Empty) Integer(1e20) → (Empty) Integer(null) → (Empty) |
| LastValidBefore(Arg1) |
Substitutes null values in a column with the last valid value before the missing value.
Example: LastValidBefore([Column]) |
| LongInteger(Arg1) | Converts the column or value to a
LongInteger.
Example: LongInteger([Column]) |
| ParseDate(Arg1, ..., Arg3) | Parses a date from a string to a
Date format. The first argument is a string or a string column containing a date.
The second argument is a format string explaining how the date is built up and the third (optional) argument is a culture code.
Examples: ParseDate("2013-09-17", "yyyy-MM-dd") ParseDate("17-okt-2013","dd-MMM-yyyy","sv-SE") |
| ParseDateTime(Arg1, ..., Arg3) | Parses date/time information from a string to a
DateTime format. The first argument is a string or a string column containing date/time information.
The second argument is a format string explaining how the information is built up and the third (optional) argument is a culture code.
Examples: ParseDateTime("2013-09-17 10:30", "yyyy-MM-dd h:mm") ParseDateTime("17-okt-2013 13:25","dd-MMM-yyyy HH:mm","sv-SE") |
| ParseReal(Arg1, Arg2) |
Parses a string to a Real value. The first argument is a string or a string column. The second (optional) argument is a culture code.
Examples: ParseReal([Column], "en-US") ParseReal("7.25") |
| ParseTime(Arg1, ..., Arg3) | Parses a time from a string to a
Time format. The first argument is a string or a string column containing a time.
The second argument is a format string explaining how the time is built up and the third (optional) argument is a culture code.
Examples: ParseTime("9:25", "h:mm") ParseTime("17:30", "HH:mm","sv-SE") |
| ParseTimeSpan(Arg1, ..., Arg3) |
Parses timespan information from a string to a TimeSpan format. The first argument is a string or a string column containing timespan information. The second argument is a format string explaining how the information is built up and the third (optional) argument is a culture code.
Examples: ParseTimeSpan("5.7:11:3.1", "d.h:m:s.f", "sv-SE") ParseTimeSpan("123 3 11 7 -5", "f s m h d") |
| Real(Arg1) | Converts the column or value to a
Real number. If the conversion fails, an error is returned.
Arg1 can be of type
Integer,
Real or
String, and the result is of type
Real.
Examples: Real(1.23) → 1.23 Real(2) → 2 Real("0%") → (Empty) Real(null) → (Empty) |
| SingleReal(Arg1) | Converts the column or value to a
SingleReal.
Example: SingleReal([Column]) |
| SN(Arg1, Arg2) | Substitutes null values. Returns
Arg1 if it is not null,
Arg2 otherwise.
Arg1 and
Arg2 can be of any type, but both must be of the same type or null. The result is of the same type as the arguments.
A common usage is to substitute null values in a column. If Arg1 is a column, Arg2 can be either a value of the same type as the contents of the column or a column with the same content type. If Arg2 is also a column, the error in Arg1 will be replaced with the value from the same row in Arg2.
Examples: SN(1, 2) → 1 SN(null, 2) → 2 SN(0/0, 2) → #NA SN([Column], 1) → 1 (if null value in column) SN([Column1], [Column2]) → (value from Column2 if null value in Column1) |
| String(Arg1) | Converts the column or value to a
String.
Arg1 can be of any type and the result is of type string.
Note: This function always uses the default value formatting. This might lead to lost precision when converting numerical values.
Examples: String(1.23) → "1.23" String(null) → (Empty) |
| Time(Arg1, Arg2,..., Arg4) | Converts the column or values to a
Time. If the conversion fails, an error is returned. If a single argument is used,
Arg1 can be of type
String or
DateTime. If a
String is specified, the time must be written in a format that Spotfire can recognize. Additionally, both the hour and the minute must be specified. See examples below. If a
DateTime is specified, the date part is removed.
If four arguments are given, they must be Integer values. The first argument is the hour, the second is the minute, the third is the second and the fourth is the millisecond. If a single Integer argument is given, the Time function will interpret the integer as ticks. Ticks are counted with the unit 100 nanoseconds, starting from the date 01-01-01 (January first, year one). See also Date and time functions and Cast method.
Examples: Time("11:37:00") → 11:37:00 Time("10") → (Empty) Time(null) → (Empty) Time(11, 30, 20, 4) → 11:30:20 Time(605000000000000000l) → 11:33:20 AM (The output formats available depend on your current locale.) |
| TimeSpan(Arg1, Arg2,...,Arg5)) | Creates a
TimeSpan from a column or values. If a single argument is given, the input column can be of type
String or
TimeSpan. If a
String is specified, the timespan must be written in the format "[-]d.h:m:s.ms".
If five arguments are given, then the first argument is the days, the second is the hours, the third is the minutes, the fourth is the seconds and the fifth is the milliseconds. The first four arguments are Integers, the last is a Real number.
Examples: TimeSpan([Column]) TimeSpan("247.5:17:11.5002") TimeSpan(247, 05, 17, 11, 500.2) |
| ToEpochMilliseconds([Arg1]) |
Converts the column or value from a datetime to a number representing the number of milliseconds that have passed since 1970-01-01.
Example: ToEpochMilliseconds([Date]) |
| ToEpochSeconds([Arg1]) |
Converts the column or value from a datetime to a number representing the number of seconds that have passed since 1970-01-01.
Example: ToEpochSeconds([Date]) |