General Build Tags

Defines the system property Current Date. The string value must use the format "YYYY/MM/DD".

Use the Spectre function table_system_property() to reference this table property. Available starting with Spectre 7.1(18).

Includes text information in the cBase. This system property can be referenced by using the Spectre function table_system_property().

The description property is available to ProDiver Reports through the variable $COMMENTS. For more information, see the ProDiver Help > About Text Variables.

Defines a system property to be associated with the cBase. A property defined on the build level (not the column level), appears in the Spectre Build Editor as a cBase property. You can refer to the property in Spectre scripts using the table_system_property() function.

NOTE: cBase properties are not related to Project Settings, that is user properties.

See Spectre Properties.

Adds a new column as calculated by the expression. The column type is determined by the value returned from the expression. Appears as "Calculated Column" in the Spectre Build GUI. Add operations can include the following tags: label, format, required-dimension, summary-type, suggested-dimensions, sort-by, sort-natural, and custom-property. (See Text, cBase, and Dive Input Build Tags.)

NOTE: You can use cPlan calcs rather than cBase adds to minimize the cBase size and the number of unique columns that would need to be loaded off of disk to handle queries. On the other hand, cPlan calcs and lookups are always slower than accessing a cBase column.

Expands one row into multiple rows, using an inclusive range defined by two columns. In the example, two columns containing months ("Start Month" and "End Month") are defined as the inclusive range for expansion. Given a table such as the following:

The expand tag processes and builds out one row for each month in between "Start Month" and "End Month" and consolidates the two input columns as the output column "Project Month". Note that other columns ("Employees" in this example) fill the newly expanded rows with identical values.

NOTES:

• The expand tag supports integer-backed data types (integer and fixed100) and date types. Fixed100 columns iterate by .01 each row.

• Datetime columns are not supported.

• Rows where the start value is greater than the end value are excluded from the output, as are null and unknown values.

• Period data types must have the same calendar to properly expand.

Joins two input flows. Join type can be:

• inner—Only rows that have matching keys are returned.
• left outer—All rows from the left flow are returned.
• outer—All rows are returned in the output flow.
• right outer—All rows from the right flow are returned.

Includes supplementary data in the cBase. The first example lookup supplements the columns in basic.txt by matching the basic.txt's "Color" column against the lookup-color.txt's "LU_Color" column, to retrieve two additional columns: Wavelength and Hex.

NOTE:

• It is a best practice to specify which columns are wanted from the lookup.
• Lookup keys need to have the same data type defined in the source and lookup.
• Multiple keys can be declared, allowing for a multi-key join.
• Lookup columns can be set as optional by using the suggested-dimension attribute with either column or include-other-columns.
• When there is no child block after text-input, all columns are brought in and their types are inferred.
• Use of a child block allows you to limit columns and apply other build operations (that is, filter, remove, lookup, and resolve-duplicate).
• Avoid operations inside a lookup block.
• If <unknown> appears as a key for a row, then <unknown> is used for all lookup columns for that row.
• Null is a valid key.
• By default, Spectre allows duplicate keys as long as the values are the same; duplicate keys with different values trigger a warning. See resolve-duplicates. Also note that having duplicate keys, even with equal values, is slower than proper lookup files.
• Lookups are case sensitive. To simulate case-insensitivity, add a column with the lower-cased version of your actual key column on either side, join on it and (optionally) remove it. See String Functions.
• If a row in a lookup's base table has a value <unknown> in a key column, then the lookup columns are assigned the value <unknown> at that row. Best practice is to not have key columns in the base table that contain the value <unknown>.

Performs lookups using the range-key tag to connect one or more of the key columns to pairs of range columns in the lookup. A range-key takes three arguments:

• Name of the key column in the main table
• Name of the begin range column in the lookup table
• Name of the end range column in the lookup table.

For a lookup row to match a main row, the main table's key value must be between the begin-range and end-range values, inclusively.

The begin-range and end-range values can be null, in which case the range is open on either side (or both sides).

Regular lookup keys can be any type except for double. Range lookup keys can be any type except for string.

Controls behavior for lookups. The tag value can be strict, any, first, or last. The selection becomes relevant whenever a lookup table has more than one row with the same set of keys (or, in the case of range-keys, when the keys cannot resolve some row in the base table unambiguously to one row in the lookup table). The specifications are:

• strict—lookup succeeds when there is exactly one match (default)
• any—any matching row in the lookup table can be used
• first—the earliest match in the lookup table is used
• last—the last match in the lookup table is used

NOTE: 

• By default (the strict option), when a build encounters duplicate lookup keys with identical values, that value is used. Otherwise, a warning is issued due to the ambiguity, and <unknown> is used instead. There is only one warning issued per lookup. Use the resolve-duplicate tag set to any, first, or last to change this behavior.
• Best practice is to avoid duplicate or <unknown> keys.

Identifies a heading column that indicates which columns are rotated to create a row, and value columns for the rotated column values. This tag names the columns that the respective row-out arguments refer to.

TIP: Elements for the rotate-output-names are labeled Row Name Column and Value Columns in the GUI.

Sorts the data on one or more columns; reverse is optional.

The sort as part of the build block sorts the union of multiple inputs; as part of an input block, sorts the inputs separately. For example, in this first snippet the list of cBases (indicated by the wildcard character) are sorted individually, not as one input.

The third snippet uses the on_previous_row function, in combination with the sort, to find the last ED visit for each account.

NOTE: Spectre uses "numeric collation" when sorting strings. When a subset of a string looks like a number, then the numeric value is considered during the sort. For example, Spectre sorts "a2" before "a10". See the sort-natural tag in Text, cBase, and Dive Input Build Tags.

TIP: Using a sort with a filter expression can allow you to squash your data—eliminate detail.