Miscellaneous Functions

Spectre includes several miscellaneous functions.

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 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 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

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)’

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

See also: