DEFINE FIELD . . . COMPUTED command
Concept Information
Defines a computed field in an Analytics table layout.
Syntax
To define a computed field:
DEFINE FIELD field_name COMPUTED expression
To define a computed field with optional parameters:
DEFINE FIELD field_name COMPUTED <IF test> <STATIC> <PIC format> <AS display_name> <WIDTH characters> <SUPPRESS> <field_note> expression
To define a conditional computed field:
DEFINE FIELD field_name COMPUTED *** BLANK_LINE *** value IF condition <value IF condition> <...n> default_value
To define a conditional computed field with optional parameters:
DEFINE FIELD field_name COMPUTED <IF test> <STATIC> <PIC format> <AS display_name> <WIDTH characters> <SUPPRESS> <field_note> value IF condition <value IF condition> <...n> default_value
Note
Multiline syntax must be structured exactly as shown in the generic syntax above and the examples below.
Parameters
| Name | Description |
|---|---|
| field_name |
The name of the computed field. Note Field names are limited to 256 upper and lowercase alphanumeric characters. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number. Analytics has a number of reserved keywords that cannot be used as field names. For a complete list, see Reserved keywords. |
| expression | A valid Analytics expression that defines the value of the computed field. |
| IF test optional |
A conditional expression that must be true in order to process each record. The command is executed on only those records that satisfy the condition. Note The IF parameter is evaluated against only the records remaining in a table after any scope parameters have been applied (WHILE, FIRST, NEXT). |
|
STATIC optional |
The field displays the same value on every line of the table until a new value is encountered. For example, if there is a Last Name field in the source data where:
In this case, "Smith" displays on six consecutive lines, then "Wong" displays on the seventh line. |
| PIC format optional |
Note Applies to numeric fields only. The display format of numeric values in Analytics views and reports. format must be enclosed in quotation marks. |
|
AS display_name optional |
The display name (alternate column title) for the field in the view. If you want the display name to be the same as the field name do not use AS. Specify display_name as a quoted string. Use a semi-colon (;) between words if you want a line break in the column title. |
| WIDTH characters optional |
The display width of the field in characters. The specified value controls the display width of the field in Analytics views and reports. The display width never alters data, however it can hide data if it is shorter than the field length. The display width cannot be less than the length of field_name, or display_name. If you omit WIDTH, the display width is set to the field length in characters. Note The characters specified by WIDTH are fixed-width characters. Every character is allotted the same amount of space, regardless of the width of the actual character. By default, views in Analytics use a proportional width font that does not correspond with fixed-width character spacing. If you want a one-to-one correspondence between the WIDTH value and the characters in the view, you can change the Proportional Font setting in the Options dialog box to a fixed-width font such as Courier New. |
|
SUPPRESS optional |
Applies to numeric fields only. Suppresses automatic totaling of numeric computed fields in Analytics reports. Totaling of some numeric fields is not appropriate. For example, a unit cost field, or a discount rate field. |
|
field_note optional |
Field note text that is added to the field definition in the table layout. field_note must be last, after all other required and optional parameters. The text cannot be multiline. Quotation marks are not required. |
| value IF condition |
Conditional computed field only.
|
| default_value |
Conditional computed field only. The value or expression to use in the computed field if none of the conditions evaluate to true. Note The decimal precision of all numeric computed values is controlled by the precision of default_value. For example, if you specify a default value of 0.00, all computed values are calculated to two decimal places, and rounded if necessary. For greater precision, increase the number of decimal places in default_value. |
Examples
Defining a computed field
You define a computed field named Value that is the product of the Cost and Quantity fields:
DEFINE FIELD Value COMPUTED Cost * Quantity
Defining a computed field with options
You define a computed field named Value_03, with several options defined. You include an IF condition that limits which records are processed by the computed field:
DEFINE FIELD Value_03 COMPUTED IF Product_Class = "03" PIC "($9,999,999.99)" AS "Value Prod Class 3" Value is cost times quantity Cost * Quantity
Defining a conditional computed field
You define a conditional computed field named Sales_tax that calculates a different sales tax depending on the state in which the transaction occurred. Transactions that occurred outside the three states have a default sales tax of $0.00.
Note
The second line must be left blank because there are no optional parameters.
DEFINE FIELD Sales_tax COMPUTED .0750 * Sale_amount IF State = "CA" .0400 * Sale_amount IF State = "NY" .0625 * Sale_amount IF State = "TX" 0.00
Defining a conditional computed field with options
You define a conditional computed field named Sales_tax_100 that calculates a different sales tax depending on the state in which the transaction occurred. The field only calculates tax on amounts of $100 or greater.
Transactions that occurred outside the three states have a default sales tax of $0.00.
Note
When you specify optional parameters, do not leave any lines blank.
DEFINE FIELD Sales_tax_100 COMPUTED IF Sale_amount >= 100 .0750 * Sale_amount IF State = "CA" .0400 * Sale_amount IF State = "NY" .0625 * Sale_amount IF State = "TX" 0.00
Remarks
For more information about how this command works, see Computed fields.
Two types of computed fields
There are two types of computed fields:
- standard computed field
A standard computed field performs the same calculation on every record in a table.
For example, in an Inventory table you could create a computed field that multiplies the value in the Cost field by the value in the Quantity field to calculate the Inventory Value at Cost for each record.
- conditional computed field
A conditional computed field is capable of performing different calculations on the records in a table, based on a set of conditions that you specify. The calculation performed on a record depends on which condition the record meets.
For example, in a Transactions table, you could create a conditional computed field that calculates sales tax using a rate that is adjusted based on the state in which the transaction occurred. Conditions such as IF State = "CA" and IF State = "NY" would test each record to identify which rate to use.
Guidelines for creating a conditional computed field
Note
When defining a conditional computed field, if you do not specify any of the optional parameters on the second line, you must leave the second line blank.
In addition to a default value, conditional computed fields require at least one conditional value. You must use the following multiline syntax to define a conditional computed field:
- optional parameters appear on the second line
- if there are no optional parameters, the second line must be left blank
- the first condition statement appears on the third line
- each additional condition statement requires a separate line
- the default value appears on the last line
Overwriting field definitions
You can overwrite a field definition in a table layout by defining a field that uses the same name as the existing field.
If SET SAFETY is ON, Analytics displays a confirmation dialog box before overwriting the existing field. To avoid interrupting a script, you can SET SAFETY to OFF, and Analytics overwrites the existing field without asking for confirmation.
