DEFINE FIELD command
Concept Information
Defines a physical data field in an Analytics table layout.
Syntax
DEFINE FIELD field_name data_type start_position length <decimals|date_format> <NDATETIME> <PIC format> <AS display_name> <WIDTH characters> <SUPPRESS> <field_note>
Parameters
| Name | Description | ||||||
|---|---|---|---|---|---|---|---|
| field_name |
The name of the 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. |
||||||
| data_type |
The data type to use when interpreting the data. For a list of supported data types, see Supported data types. For example, invoice numbers may be stored as numeric values in the data source. To treat these values as strings rather than numbers, you can define the field as character data instead. |
||||||
| start_position |
The starting byte position of the field in the Analytics data file. Note
For Unicode data, typically you should specify an odd-numbered starting byte position. Specifying an even-numbered starting position can cause characters to display incorrectly. |
||||||
| length |
The length of the field in bytes. Note
For Unicode data, specify an even number of bytes only. Specifying an odd number of bytes can cause characters to display incorrectly. |
||||||
| decimals
optional |
The number of decimals for numeric fields. |
||||||
| date_format
optional |
The date format in the source date fields. For datetime or time fields, use PIC format instead. You can also use PIC format for date fields. If the source data includes separators such as slashes, you must include the separators in date_format. For example, if the source data is 12/31/2014, you must enter the format as MM/DD/YYYY. Do not enclose date_format in quotation marks. |
||||||
|
NDATETIME optional |
Date, datetime, or time values stored in a numeric field are treated as datetime data. NDATETIME requires that you also specify the source datetime format using PIC format. |
||||||
| PIC format optional |
Note Applies to numeric or datetime fields only.
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 |
Only applies to numeric fields. Suppresses automatic totaling of a numeric field 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. |
Examples
Defining a character field
Defines a character field called ProdDesc. The column title in the view is Product Description.
non-Unicode Analytics
- Starts at: byte 12 (character position 12)
- Length: 24 bytes (24 characters)
DEFINE FIELD ProdDesc ASCII 12 24 AS "Product Description"
Unicode Analytics, extended ASCII (ANSI) data
- Starts at: byte 12
- Length: 24 bytes (24 characters)
DEFINE FIELD ProdDesc ASCII 12 24 AS "Product Description"
Unicode Analytics, Unicode data
- Starts at: byte 13
- Length: 48 bytes (24 characters)
DEFINE FIELD ProdDesc UNICODE 13 48 AS "Product Description"
Defining a numeric field
Defines a numeric field called QtyOH. In the view, the column uses the specified display format, and the title is Quantity On Hand.
- Starts at: byte 61
- Length: 10 bytes
- Decimal places: none
DEFINE FIELD QtyOH NUMERIC 61 10 0 PIC "(9,999,999)" AS "Quantity On Hand"
Defining a datetime field from character data
From source character data, the first two examples below define a datetime field called Transaction_date. In the source data, the date format is DD/MM/YYYY. No column title is specified, so the column title defaults to using the field name.
- Starts at: byte 20
- Length: 10 bytes
Here, the date format is specified using date_format:
DEFINE FIELD Transaction_date DATETIME 20 10 DD/MM/YYYY
Here, the date format is specified using PIC format:
DEFINE FIELD Transaction_date DATETIME 20 10 PIC "DD/MM/YYYY"
When defining datetime fields that include time data, you must use PIC format,
The example below defines a datetime field called email_timestamp. In the source data, the datetime format is YYYY/MM/DD hh:mm:ss-hh:mm.
- Starts at: byte 1
- Length: 25 bytes
DEFINE FIELD email_timestamp DATETIME 1 25 PIC "YYYY/MM/DD hh:mm:ss-hh:mm"
Defining a datetime field from numeric data
From source numeric data, defines a datetime field called Receipt_timestamp that has the specified datetime format in the source data.
- Starts at: byte 15
- Length: 15 bytes
DEFINE FIELD Receipt_timestamp DATETIME 15 15 PIC "YYYYMMDD.hhmmss"
Defining a "numeric" datetime field
From source numeric data, defines a numeric field called Receipt_timestamp that has the specified datetime format in the source data.
The NDATETIME parameter allows datetime values stored in the numeric field to be treated as datetime data by Analytics.
- Starts at: byte 15
- Length: 15 bytes
- Decimal places: 6
DEFINE FIELD Receipt_timestamp PRINT 15 15 6 NDATETIME PIC "YYYYMMDD.hhmmss"
Defining a physical data field that reads mainframe Packed data
You can use the NDATETIME option to create a physical data field that reads date values from a Packed numeric field.
Analytics cannot recognize a date in a number that is compressed into fewer bytes than one per digit and that displays no date format. Consequently, you must unpack the number with NDATETIME to obtain the full number of digits, then specify the date format with PIC.
To accurately indicate which numbers represent the day, the month, and the year, you specify the same date format as the one in the Packed record layout:
DEFINE FIELD date_field_name NUMERIC 1 8 0 NDATETIME PIC "YYYYMMDD"
Remarks
For more information about how this command works, see Physical fields.
Overwriting fields in a script
You can overwrite a field in a table layout by defining a field that uses the same name as the existing field. If SET SAFETY is ON, a confirmation dialog box appears before overwriting the existing field.
To avoid interrupting a script, you can SET SAFETY to OFF. The existing field is overwritten without additional confirmation.
Supported data types
| Data category | Data type |
|---|---|
| Character | ASCII |
| CUSTOM | |
| EBCDIC | |
| NOTE | |
| PCASCII | |
| UNICODE | |
| Numeric | ACCPAC |
| ACL | |
| BASIC | |
| BINARY | |
| FLOAT | |
| HALFBYTE | |
| IBMFLOAT | |
| MICRO | |
| NUMERIC | |
| PACKED | |
| UNISYS | |
| UNSIGNED | |
| VAXFLOAT | |
| ZONED | |
| Datetime | DATETIME |
| Logical | LOGICAL |
