General Build Tags

Defines the block for the Build definition for the cBase. Parameters can be declared here. See the take-parameter tag below.

For example:

build {

...

}

Adds a calendar for the Build.

The first sample declaration reformats the standard display from YYYY/MM to YYYY-MMM.

The second sample creates a fiscal calendar from the indicated cBase.

The last sample builds the fiscal calendar from the named calendar text file.

NOTE:Available in 7.0(35) and later.

See Calendar Declarations.

For example:

calendar "modified-standard" type="standard" {

period "year-month" format="YYYY-MMM"

}

 

calendar "fiscal" type="custom" {

cbase-input "fiscal.cbase"

}

 

calendar "fiscal" file="fiscal.calendar"

Defines custom properties for the cBase. Custom properties are not used by Spectre but are available for users to add their own metadata to Spectre objects. Custom properties can be referenced in Spectre expressions using functions like column_custom_property, Miscellaneous Functions, or calc_custom_property, depending on the context.

TIP:

• Custom properties in a cBase are available to ProDiver Reports using the variable $MODEL_VAR() (for example, $MODEL_VAR(color)).

• The custom-property tag, if used to define a footer, can be accessed in ProDiver via report macros using either $MODEL_VAR() or $MODEL_FOOTER.

See Spectre Properties.

For example:

custom-property "color" "blue"

custom-property "footer" "Confidential"

Specifies a default calendar. The default calendar can be set to any declared calendar (including custom calendars), or standard, offset <month>, or iso 8601. The default calendar is standard unless declared otherwise.

NOTE:Available in 7.0(35) and later.

For example:

default-calendar "offset november"

default-calendar "modified-standard"

Includes text information in the cBase. This system property can be referenced by using the Spectre function Miscellaneous Functions.

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

For example:

property description "YTD cBase"

Defines an extract-time that describes when the input data was extracted from the source data. 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 should be given a date in the form of "YYYY-MM-DD" or a timestamp in the format of "YYYY-MM-DD HH:MM:SS", or the string "now". If only a date is given, the time will be set to 12 noon of that date.

The extract-time value is stored as the Extract Timestamp system property in the cBase. It is used in Diver Reports or Report Palettes to populate the $EXTRACT_DATE and $EXTRACT_TIME string variables. For more information, see About Text Variables in the ProDiver Help. To use the extract time in Spectre scripts, see the Miscellaneous Functions Spectre function.

NOTE: The build time is recorded automatically as the Build Timestamp system property in the cBase. This build time is available in Diver Reports using $BUILD_DATE and $BUILD_TIME, and in Spectre scripts using the build_timestamp() function.

See Miscellaneous Functions.

For example:

extract-time "2010-05-23 11:34:12"

extract-time "2010-05-25"

extract-time "now"

 

build {

take-parameter "Extract Time" default="now"

text-input "basic.txt"

extract-time "$(Extract Time)"

...

}

Defines a text file for calendars. See the calendar tag for more information.

For example:

calendar "fiscal" file="fiscal.calendar"

Ensures correct handling of monetary and date data. Include this tag if you are working in a non-English setting, or to ensure consistency for multiple cBase inputs. You can reference this system property using the Spectre function Miscellaneous Functions.

NOTE:

• Combining cBases with different locales is not possible in 7.0, but it can be done in Build scripts starting with Spectre 7.1.
• When en_US_POSIX is the specified or default locale, Spectre uses en_US.

For example:

locale "sv_SE"

 

build {

text-input {

...

}

locale "en_US"

output "<file name>.cbase"

}

Applies operations to the data. Once the input source for the cBase has been specified, operations such as lookups, filtering, sorting and adding can be defined on the data to create new columns. See the Build Operation Tags table below.

For example:

build {

text-input "/common/basic.txt"

add "Weight %" `value("Weight") / sum(value("Weight"))`

add "Source Index" `row_number()`

output "basicplus.cbase"

}

Defines the location and file name for the cBase output. If the path string is more than a file name, the sub directory must already exist relative to the current working directory. For example, a script such as <project>/build/xyz.build with the line output cbases/xyz.cbase writes to <project>/build/cbases/xyz.cbase.

For example:

output "/cbases/sales.cbase"

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 Miscellaneous Functions function.

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

See Spectre Properties.

For example:

build {

text-input "/common/basic.txt" {

...

}

property "Description" "This is a description for the cBase"

Declares the parameters as needed so that their values can be used in subsequent lines.

For example:

build {

take-parameter "Input File"

text-input "$(Input File)" {

column ...

}

}

Adds a new column as calculated by the expression. The column type is determined by the value returned from the expression, appearing as "Calculated Column.". 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.

For example:

add "Double Value" `value("Price") * 2`

add "Weight %" `value("Weight") / sum(value("Weight"))`

add "Source Index" `row_number()`

add "Invoice Month" `lpad(string(extract(value("Invoice Date"), "month")),2,"0")`

add "Order YearMo" `period("gregorian month", value("Order Date"))`

add "YearMo" `period("Fiscal", "year-month", value("Date"))`

Filters the input based on a Spectre expression. The first example pulls in the first 80 rows. In the second example, Car Filter is the parameter passed in and Category is the column, so the data is filtered accordingly. The third example filters two columns on the values in the expression.

Filter statements can be placed inside of individual inputs as well as outside. When inside, the filter is limited to just that input; otherwise it applies to all the inputs.

For example:

filter `row_number() <= 80`

filter `param_match("Car Filter", "Category")`

filter `value("Color") = "Red" or value("Direction") = "North"`

text-input "companies.txt" {

filter `value("Sales Region") = "Boston"`

}

cbase-input "districts.cbase"

filter `value("Sales Region") = "Boston"`

Keeps only the specified columns from the input. Useful when there are many columns and only a select few are needed. Keep must be part of the input block.

For example:

build {

cbase-input "budget.cbase" {

keep {

column "Account"

column "Fee Total"

}

}

}

Names the left and right flow columns to match on, plus the column to include in the output. This attribute is part of a join block.

For example:

key "Customer ID" "Customer ID" "Cust ID"

Indicates that all columns brought in via the lookup are flagged as Infos off of the key dimension. This attribute is part of the lookup block.

For example:

keys-are-required-dimensions

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-duplicates).
• Avoid operations inside a lookup block.
• If <unknown> appears as a key for a row, <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, 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>.

build {

text-input "basic.txt"

lookup {

text-input "lookup-color.txt"

key "Color" "LU_Color"

column "Wavelength"

column "Hex"

}

output "lookup.cbase"

}

 

lookup {

text-input "lookup.txt"

key "A" "A"

column "B" suggested-dimension=false

}

 

lookup {

text-input "lookup.txt" {

include-other-columns suggested-dimension=false

}

key "A" "A"

column "B"

}

 

lookup {

consolidate {

cbase-input "lookup-pre.cbase"

cbase-input "lookup-post.cbase"

}

key "A" "A"

column "B"

}

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.

For example:

build {

text-input "base.txt"

lookup {

text-input "lookup.txt"

range-key "Date" "Start Date" "End Date"

key "Code" "Code"

}

output "result.cbase"

}

 

cplan {

text-input "some_integers.txt"

lookup {

text-input "neighborhoods.txt"

range-key "number" "small" "big"

}

}

Removes the named column.

For example:

remove "Account"

remove "Fee Total"

Renames a column. In this example, the Mood column is renamed Attitude. In the Spectre Build GUI, this appears as an Alias.

For example:

rename "Mood" "Attitude"

Replaces a column's contents based on a Spectre expression. In the first example, the weight column contents is replaced by the result of multiplying it by 100. The second example replaces the phone number with a formatted version. The third example replaces the double value in the input with a fixed100 value in the output.

Replace is a type of add operation.

For example:

replace "Weight" `value("Weight") * 100`

replace "Customer Phone" `concat("(",substr(value("Customer Phone"),1,3),") ", substr(value("Customer Phone"),4,3), "-",substr(value("Customer Phone"),7,4))`

column "Profit" type="double"

replace "Profit" `fixed100(value("Profit"))`

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.

For example:

lookup {

text-input "presidents.csv"

key "Name" "Name"

column "Rank"

resolve-duplicates "first"

}

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.

For example:

cbase-input "demo_drs*.cbase"{

sort {

column "Customer"

column "City"

}

}

 

sort column "Profit" reverse=true

 

sort {

column "Account ID"

column "ED Arrival Date" reverse=true

}

 

add "Last ED Visit for Account" `if(on_previous_row(value("Account ID"))=value("Account ID"),"","Y")`