Data Fields
To define data fields, you will work with the [Edit Fields/Expressions] tab of the Table Layout tab. Use the [Edit Fields/Expressions] tab to define physical fields from the source data as well as computed fields that you can use for analysis in Analyzer.
The [Edit Fields/Expressions] tab can display three different screens with information about data fields:
|
•
|
Fields/Expressions screen lists all the fields that have been defined. |
|
•
|
Data Field Definition screen lets you define physical fields. |
|
•
|
Computed Fields screen lets you define virtual computed fields. Computed fields are defined by expressions and the term Expressions in this tab refers to computed fields. |
When you click the [Edit Fields/Expressions] tab, Analyzer always displays the Fields/Expressions screen first. The other screens are displayed only when you click the appropriate buttons in this screen.
Fields/Expressions List View
To define fields, click the [Edit Fields/Expressions] tab. Analyzer displays the Fields/Expressions list View.
The Fields/Expressions list View in the upper portion of this window identifies all of the defined physical and computed fields. Because the fields have not yet been defined for this file, the Fields/Expressions list View is still blank. The data display area at the bottom displays data as it appears in the source file.
Data Field Definition Screen
To define physical data fields, first display the Data Field Definition screen: click in the data area on the start of the field and hold down the mouse button. Alternatively, click [Add a New Data Field]. The Fields List screen changes to the Data Field Definition screen.
Defining Physical Data Fields
You define fields that exist in the source data file by identifying for Analyzer their start position, their length, their name, their character set, their field type, number of decimals for numeric fields and the input format for date and/or time fields. To do this you will need to have a file layout that provides this information for the source data file. In addition you can specify an If filter, an optional alternate column title, the numeric display format and certain other Advanced options. You can do all of this by double-clicking on the field in the [Edit Fields/Expressions] tab of the Edit/Table Layout menu.
To define a physical field in the source data file, follow the steps below:
|
1.
|
In the data display area, click in the first data byte position and drag the mouse to the end of the field. |
|
•
|
For example, if the first five bytes contain the Vendor number, click and drag in the data area from byte positions 1 to 5. Alternatively, you can specify this information using the Start and Length text boxes. |
|
2.
|
Release the mouse button. Analyzer highlights the field, identifies it as an ASCII character field and the cursor begins to blink in the Name text box. |
|
•
|
Alternatively, you can specify this information using the Type drop-down list. However, this is not recommended, as you may choose an invalid type. |
|
3.
|
Enter the name of the field in the Name text box. Spell out field names as they are in the source data file. |
|
4.
|
Enter the name of an alternate title in the Column Title text box (optional). Analyzer lets you give fields alternate column titles. For example, you can give the title Vendor Number to a field named VENDNO in the data file. |
|
5.
|
Click [Accept Entry] or press Enter. Analyzer records the information you specified about the field. |
|
•
|
If you make a mistake or want to cancel the settings you have specified, click [Clear Entry] or press ESC to return to the Fields List screen. |
|
6.
|
Repeat these steps to define the fields you are interested in, one by one. |
Note: When naming fields, do not use words that Analyzer uses for special purposes and that cannot be used as field names. For a list of reserved keywords, see Reserved Keywords.
Analyzer assists with the field definition process by doing the following automatically:
|
•
|
When you click and drag in the data area, Analyzer recognizes that you are defining a physical data field. |
|
•
|
As you drag the mouse, Analyzer analyzes the data and continuously updates the Valid Field Types list to show only the valid field types, listed in descending order of likelihood. |
|
•
|
When you release the mouse button, the start and length of this new field are entered into the appropriate text boxes and the data type is set to the most likely value, listed at the top of the Valid Field Types list. |
|
•
|
The ruler shows the data areas you have defined in reverse video. |
Advanced Field Edit
To access Advanced field attributes for the field being edited, click the Advanced button.
Depending on the type of field being edited, some options will be available and others greyed out:
|
•
|
Suppress Totals (numeric fields) - prevent totalling of numeric field when being accumulated in a command |
|
•
|
Static - retain value of field from last record if current field value is empty |
|
•
|
Date - indicates that a numeric (packed) field should be treated as a date field |
|
•
|
Control Total - ensures control totals are generated for numeric field when a command is run |
|
•
|
Default Filter - used on logical fields (filters) to indicate that this filter is to be set on by default whenever the table is opened |
|
•
|
Hidden - field should be hidden in field lists for commands and View |
|
•
|
Key Field - indicates that field is a key field in relations/joins |
|
•
|
Unsigned - indicates that binary numeric field is unsigned |
|
•
|
Condition - specifies filter criteria to indicate which records are valid for this field definition, typically used for print image or other multiple record type files |
|
•
|
Offset - used for bucketed files to indicate the relative offset of the field start position |
|
•
|
Decimal Place Symbol - indicates the decimal character used in numeric field - overrides global numeric decimal in preferences |
Defining Overlapping Fields
You can define overlapping fields and the overlapping fields can be assigned different data types. For example, you can define bytes 1 to 10 as a NUMERIC data type, then define bytes 5 to 9 as an ASCII data type.
You can do this when the fields overlap in the source data file. For example, you could define a field with an input date format of YYMMDD and then separately define the bytes that represent the month as a character field called Month. This allows you to access the entire date in one field for aging purposes, as well as month names in another for generating monthly totals.
Note: You can manually define overlapping fields in a delimited file, not just a flat file.
Modifying Fields in Delimited and Relational Data Sources
In delimited and relational data sources, you can modify the width of fields. Analyzer automatically adjusts all field definitions that follow the adjusted field appropriately. This is particularly useful for adjusting the length of long memo type fields.
Numeric Fields
When you select data for a numeric field, Analyzer treats it as ASCII by default. After you have specified the field length, select NUMERIC from the Type drop-down list.
Once you select the NUMERIC field type, the cursor moves to the Dec. (decimals) text box so that you can specify the number of decimal places from the file layout.
You can define several formatting options for numeric fields:
|
•
|
Format. This option controls the way the numbers in this field are formatted in Views and in reports. When no format is shown, the default display format specified in the Options dialog applies to the data in this field. The format you specify here overrides the default format. Use a format from the drop-down list or customize a format from the list after you have selected it. |
|
•
|
Suppress Totals. Select this Advanced option to prevent the values in this field from being totaled. Analyzer automatically totals numeric fields in reports. Some numeric fields contain information that is not to be totalled, such as prices or account numbers. |
|
•
|
Control Total. Select this Advanced option to identify the numeric field as a control total field. The input and output totals of a control total field are included in the file history. Control Total is used as a check to verify that files that are used over a period of time are complete and consistent from one session to the next. It is also used to reconcile data to external sources. |
|
•
|
Unsigned. Select this Advanced option to identify an unsigned Micro or Binary fields. For more information see Binary. and Micro. |
|
•
|
Decimal Place Symbol. Select this Advanced option to override the global numeric settings for the decimal place symbol (Tools/Options/Numeric) at a field level. |
Date and Time Fields
Readable date and times can be defined as either datetime, character or numeric fields. When you manually select readable data for a date and/or time field, Analyzer treats it as character (ASCII or EBCDIC) by default. To have Analyzer display the data in this field as date information, select DATETIME from the Type drop-down list, then specify the input format.
An input format for dates identifies which characters or numbers represent the day, the month and the year and which ones represent separators. An input format for time specifies hours, minutes, days, separators and AM/PM indicators. You can manually enter the input format or select one from the Format drop-down list and modify it to represent the way the date or time information is stored in the data file. For more detailed information about date and time fields, see DateTime.
Note: The input format does not affect the way Analyzer displays dates and times in Views and reports. Times are always displayed in 24 hour clock mode. For information about how to set the display format for dates in Analyzer, see Date Options.
Resolving Discrepancies with the File Layout
If your file layout does not agree with Analyzer’s analysis or if you have made a mistake, you can change any of the field values in the text boxes or the drop-down lists. When you have made your changes, click [Accept Entry] or press Enter. Click [Clear Entry] or press ESC to return to the Fields/Expressions list View screen.
Using the Fields List View
Each time you finish defining a field and click [Accept Entry], Analyzer returns you to the Fields List screen of the [Edit Fields/Expressions] tab. When you have finished defining the fields, the Fields List screen will contain all the fields you have defined.
The Fields/Expressions list View displays complete information about each field you have defined. You can use it to browse fields by their name, alternate title, type, position, category and so on. You can rearrange the order of the columns in the list View and you can sort the list by clicking the column titles.
For more information about using field list Views in Analyzer, see Using List Views.