Miscellaneous Functions

Spectre includes several miscellaneous functions.

Return a cryptographic hash value for the given string. The numeric hash is encoded into a base-64 string.
blake2 (expression : any) : any: string
NOTE: Cryptographic hash functions do not produce unique numbers, but are highly likely to produce different numbers for different inputs.

For example:

Suppose a build includes the following expression:

column "myhash" `blake2("Hello")`

This could result in a column value:

"7xXq+S1eM1NFo+HZd7x9h5fD0nVxfMGxCveck82gGusqDFm8AuK9+TgP0bVOueFmkCaTDMwkvUl0jmX5prLuaA=="

This is the blake2b hash function from https://blake2.net/ in its default mode of 512bit hashes, then encoded to a string with base64.

Return the date and time when the cBase was built. Build time is automatically captured as part of the meta-data for a cBase. It is stored as Coordinated Universal Time (UTC). The build-time value is stored as the Build Timestamp system property in the cBase.
build_timestamp() : datetime

For example:

Suppose a Dive includes the following expression:

column "Built" `build_timestamp()`

This could result in:

Build Timestamp sample

Return the value of a custom property of a calculation.
calc_custom_property(calc_name : string, property_name : string, fallback : string) : string
Returns the string value of the property_name on the specified calc_name. If the property_name does not exist, this function returns the fallback string.

For example:

Suppose a cPlan includes the following expression:

calc "Profit" `calc("Revenue") - calc("Cost")` {

custom-property "Currency" "USD"

}

Then, `calc_custom_property("Profit", "Currency")` returns "USD".

Return the value of a system property of a calculation.
calc_system_property(calc_name : string, property_name : string, fallback : string) : string
Returns the string value of the property_name on the specified calc_name. If the property_name does not exist, this function returns the fallback string.

For example:

Suppose a cPlan includes the following expression:

  calc "max_units" `max(value("Units"))` label="Max Units" {
    property "Description" "The biggest units number"
  }

Then, `calc_system_property("max_units", "Description")` returns "The biggest units number".

Return the label of a calculation.
calc_label(calc_name : string) : string

For example:

Suppose a cPlan includes the following expression:

calc "PFT" `calc("Revenue") - calc("Cost")` label="Total Profit"

Then, `calc_label("PFT")` returns "Total Profit".

Handle an error in a sub expression by substituting another value. Use for better exception handling. Unknown values propagate unless explicitly caught with the catch function. This is by design.
catch(expression : any, if_error : any) : any
The expression and if_error data types must be convertible to a common type.

For example:

`catch(parse_integer(calc("Volume")),0)` where the column Volume contains some integer and some string values, the strings are treated as zero.

Return the first non-null value in a list of values, or null if all values are null.
coalesce(value1 : any, value2 : any, ... : any) : any
This function is shorthand for a chain of if functions, so the arguments must be convertible and the type of the return value is coerced just as the chain of if functions would. Use the coalesce function to translate a null value to a fallback value.

For example:

`coalesce(null, 1, 2)` returns 1

calc "INVOICE_KEY Count" `coalesce(dimcount("INVOICE_KEY"), 0)` returns the number of invoice keys or zero

add "Phone" `coalesce(value("Home Phone"), value("Work Phone"), value("Cell Phone"))` returns the first non-null phone number

Return the value of a custom property of a column.
column_custom_property(column_name : string, property_name : string, fallback : string) : string
Returns the string value of the property_name on the specified column_name. If the property_name does not exist, this function returns the fallback string.

For example:

Suppose a Build script includes the following expression:

add "Profit" `value("Revenue") - value("Cost")` {

custom-property "Currency" "USD"

}

Then, `column_custom_property("Profit", "Currency")` returns "USD".

Return the value of a system property of a column.
column_system_property(column_name : string, property_name : string, fallback : string) : string
Returns the string value of the system’s property_name on the specified column_name. If the property_name does not exist, this function returns the fallback string.

For example:

Suppose a Build script includes the following expression:

add "Profit" `value("Revenue") - value("Cost")` format="$#,0.00"

Then, `column_system_property("Profit", "Format")` returns "$#,0.00".

Return the value of a custom property of a cPlan.
cplan_custom_property(cplan_name : string, property_name : string, fallback : string) : string
Returns the string value of the property_name on the specified cplan_name. If the property_name does not exist, this function returns the fallback string. If not provided, a string-typed null is returned.

For example:

Suppose a cPlan script includes the following expression:

custom-property "Custom Prop" "My cPlan Property"

Then, `cplan_custom_property("Custom Prop")` returns "My cPlan Property".

Return the value of a system property of a cPlan.
cplan_system_property(cplan_name : string, property_name : string, fallback : string) : string
Returns the string value of the system’s property_name on the specified cplan_name. If the property_name does not exist, this function returns the fallback string. If not provided, a string-typed null is returned.

For example:

Suppose a cPlan script includes the following expression:

property "Description" "test cPlan for properties"

Then, `cplan_system_property("Description")` returns "test cPlan for properties".

Return the project location of the current file.
current_file() : string

For example:

`current_file()` might return: project://My Project/testing/current-file.dive

Return the username for the current user.
current_user() : string

For example:

`current_user()` might return "jane_doe"

calc "Logged In User" `current_user()` creates a calculated column in a cPlan that contains the DiveLine userid of the current user.

Emit an error. Produces an error value, and sends the specified message to the log.
error(message : string) : error
This function is only sensible and therefore only valid as one of the legs of an if function. Use for better exception handling.

For example:

`if(is_null(value("Cost")), error("Uh-oh, cost is null!"), value("Cost"))` when true, results in a blank value and the "Uh-oh" error in the log

calc "error" `if(calc("Mood") != "Dopey", error("not dopey"), "OK")` if true, creates a message in the log prefaced with ERROR:, followed by the error string ("not dopey" in this example); result of the error calc is UNKNOWN.

Return the date and time string when the data for the cBase was extracted from its source. Extract time is added to the meta-data for a cBase by using the extract-time tag in a Spectre Build script. The string value can be passed in as parameter to the Build script, computed beforehand in the Production script that runs the build. The extract-time value is stored as the Extract Timestamp system property in the cBase.
extract_timestamp() : string

For example:

Suppose the Spectre Build included extract-time "7/1/2020 12:34:56". After the build, the cBase properties include:

Extract Timestamp property in cBase

This value can be retrieved using extract_timestamp(). For example, a Dive can include the following expression:

column "Extract" `extract_timestamp()`

Return the contents of a file as a string.
file_contents(file_name : string, encoding: string) : string
This function takes one or two arguments. With one argument, returns the contents of the specified file as a string, using the system's current default encoding. With two arguments, returns the contents of the specified file as a string, using the specified encoding.

For example:

date(rtrim(file_contents("date.txt"))) reads the contents of date.txt, strips off any trailing white space (such as a newline character), and converts the result into a date by using the default date format "YYYY/MM/DD". This is a good way to specify a time-series anchor for data extract processes that are not necessarily run daily.

calc("Cost") * parse_float(rtrim(file_contents("multiplier.txt"))) reads the contents of multiplier.txt, strips off any trailing white space, converts the result into a floating point number, and multiples that number by the result of the "Cost" calc.

Filter the records that a summary expression runs on. See also top and parent.
filter(expression : any, predicate : boolean) : any
This function runs the summary expression on the input records in the current working set that pass the detail filter predicate. “Current working set” means the working set for the window row that is being computed, or the working set that is inherited from enclosing calls to top or filter. The functions top and parent always replace the current working set, so there is no cumulative effect if these functions are used inside a filter. This means that `filter(parent(expression), (predicate)` is equivalent to `parent(expression)`.

For example:

`filter(calc("Revenue"), value("State") = "Ohio")` runs the "Revenue" calc only on detail rows where the "State" column is "Ohio". This is the preferred way to do a filtered calculation.

`top(filter(calc("Revenue"), value("State") = "Ohio"))` returns the "revenue" calc run on all rows in the cBase where the "State" column is "Ohio". This produces the same value on every window row, because the call to top replaces the current working set with the entire cBase.

`parent(filter(calc("Revenue"), value("State") = "Ohio"))` returns the "Revenue" calc run on detail records in the window's working set where the "State" column is "Ohio". This produces the same value on every window row, because the call to 'parent' replaces the current working set with the entire window's working set.

`filter(sum(value("Revenue")), value("State") = "Ohio")` returns the sum of "Revenue" where "State" is "Ohio".

`filter(calc("Rev"), value("State") = "Ohio") * 100 / top(calc("Rev"))` returns the percentage of the overall "Rev" value contributed by the part of the current working set where the "State" column is "Ohio".

`filter(filter(expression, pred1), pred2)` is equivalent to `filter(expression, pred1 and pred2)`.

`filter( dimcount("OrderNbr"), value("DeliveryDate") = today() )` does a filtered dimcount using a date function. This expression can be used in a cPlan and the calc accessed by a Marker. The expression can also be used in ProDiver's Edit Column dialog.

Determine if the current user is in a specified group.
group_match(name : string) : boolean

For example:

`group_match("Manager")` returns true when the current user is in the "Manager" group; returns false when the current user is not in the "Manager" group.

Select a named input for the subexpression to run on.
input(input_name : string, expression : any) : any

For example:

Assuming cbase-input "data-A.cbase" name="A" and cbase-input "data-B.cbase" name="B",

then `input("B", sum(value("Units")))` returns the sum of "Units" in the data-B.cbase.

`input("Sales", calc("Revenue"))` returns the sum of the value for the Revenue column in the source named "Sales".

Determine the distance in kilometers between two latitude-longitude pairs. Latitude and longitude are specified in decimal degree format.
kilometer(lat1 : numeric, lon1 : numeric, lat2 : numeric, lon2 : numeric ) : double

For example:

`kilometers(42.3731998,-71.1137217,42.5054961,-71.194671)` returns 16.1409561.

Determine the distance in miles between two latitude-longitude pairs. Latitude and longitude are specified in decimal degree format.
miles(lat1 : numeric, lon1 : numeric, lat2 : numeric, lon2 : numeric ) : double

For example:

miles(Maximum[sum_long], Maximum[sum _lat], Info[longitude], Info[latitude]) uses the maximum of the summary values in the cPlan, along with latitude-longitude values from another cBase in the cPlan to calculate the miles.

Determine if a value is not null.
notnull(value : any) : boolean

For example:

`notnull(0)` returns true.

`notnull("")` returns false.

filter `notnull(value("city"))` filters out null city values; this is equivalent to filter `value("city") != ""`

calc "test" `notnul(value("Units"))` adds a calc column in a cPlan with true(1) if Units is not null, or false(0) if Units is null

Run the specified expression on the values from the previous row.
on_previous_row(expression : any, start : any) : any
The value for the first row is the start or initial value, which is usually zero. Specifying a start value is optional.

For example:

`on_previous_row(value("A"))` returns the value of A on the input row before the current row

`on_previous_row(value("A"), 0)` returns the value of A on the input row before the current row

`on_previous_row(on_previous_row(value("A"))` returns the value of A two rows before the current row

add "Last ED Visit for Account" `if(on_previous_row(value("Account ID"))=value("Account ID"),"","Y")`returns, when run against sorted data, the last ED visit for each account

add "break" `value("Dim1") != on_previous_row(value("Dim1"))` could simulate the Integrator "break" functionality by allowing you to add a column when a break in multi-sorted data occurs, setting the data up for a filter or calc

add "Sum" `on_previous_row(value("Sum")) + value("Value")` refers to the column being constructed in order to create a cumulative column

add "X" `on_previous_row(value("X")) + 1` in a Build script, this calc uses the new column X to create a cumulative counter based on itself, similar to a persistent calc in Integ

TIP: `on_previous_row()` expressions that refer to the current column also work with strings.

Run a summary expression over all records in the window. See also top and filter.
parent(expression : any) : any
This function runs the summary expression on the input records in the window's working set, ignoring the current window row. Like top, this function changes the working set that is used for the sub expression, but instead of using the entire cBase, the function reverts to the working set that was used by the entire current window. For top-level dives, top and parent are equivalent, but if you have a parent window, then parent uses the working set that is filtered by the parent window. Note that `parent(parent(expression))` is equivalent to `parent(expression)`.

For example:

`parent(calc("Revenue"))` returns the same value as an unfiltered totals row would for Revenue.

`parent(filter(calc("Revenue"), value("State") = "Ohio"))` returns the "Revenue" calc run on detail records in the window's working set where the "State" column is "Ohio". This produces the same value on every window row, because the call to 'parent' tells it to replace the current working set and with the entire window's working set.

`parent(calc("Rev")) * 100 / top(calc("Rev"))` returns the percentage of the overall "Rev" value contributed by the current window's total "Rev".

`calc("Rev") * 100 / parent(calc("Rev"))` returns the percentage of the window's total "Rev" value contributed by the current working set.

`filter(calc("Rev"), value("State") = "Ohio") * 100 / parent(calc("Rev"))` returns the percentage of the window's total "Rev" value contributed by the part of the current working set where the "State" column is "Ohio".

The Spectre expression: ‘parent(calc(“Units”))‘ computes the same number as ProDiver’s: ‘parent(units)’

Return the value of a custom property of the current table.
table_custom_property(property_name : string, fallback : string) : string
Returns the string value of the custom property_name for the current cBase. If the property_name does not exist, this function returns the fallback string.

For example:

Suppose the Build script includes:

custom-property "Table Prop" "B"

Then a dive with:

column "Table Prop" `table_custom_property("Table Prop", "unknown")` returns B in the Table Prop column

Return the value of a system property of the current table.
table_system_property(property_name : string, fallback : string) : string
Returns the string value of the system property_name for the current cBase. If the property_name does not exist, this function returns the fallback string.

For example:

The build timestamp property is automatically set. A dive with:

add "UTS" `table_system_property("Build Timestamp", "Build?")` returns something like "1560186508" as the column value. This system property returns a Unix timestamp in string form.

Run a summary expression over all detail records in the cPlan or cBase. See also parent and filter.
top(expression : any) : any
The functions top and parent always replace the current working set, so there is no cumulative effect if they are used insider another top or parent. This means that top(parent(expression)) is equivalent to parent(expression). Also, parent(top(expression)) is equivalent to top(expression) because the call to top replaces the working set that parent produced. Note that `top(top(expression))` is equivalent to `top(expression)`.

For example:

`top(calc("Rev"))` runs the "Rev" calc over all cBase rows. This is what you would get in the totals row for "Rev" on any top-level dive.

`top(filter(calc("Rev"), value("State") = "Ohio"))`returns the "Rev" calc run on detail records in the window's working set where the "State" column is "Ohio". This produces the same value on every window row, because the call to top replaces the current working set with the entire cBase.

`top(max(value("Profit")))` returns the largest value of "Profit" in the entire cBase.

`top(max(value("Ship Date")))` returns the last ship date in the cBase.

`top(sum(value("Rev")))` returns the sum of "Rev" over the entire cBase.

`calc("Rev") * 100 / top(calc("Rev"))` returns the percentage of the overall "Rev" value contributed by the current working set.

NOTE: The top function cannot be used in Build scripts when creating calcs with the add tag.
Return the value of a column at the current input record. That is, the value() function takes a string and interprets it as a column name.
value(column_name : string) : any

For example:

`value("State")` returns the contents of the state column

Return the value of a column if the column exists. Otherwise return a fallback expression.

value_or(column_name : string, fallback : string) : any

For example:

`value_or("State", "Province")` returns the contents of the State column if it exists. Otherwise, returns the contents of the Province column.

See also: