Managing Field Records in Advanced View
A field is a basic building block for record types. These are named objects based on a simple data type. A field can consist of a single field, a structured field, or a reference to another unique record type.
You can do the following:
• Add and delete field
• Reorder fields in the grid
• Lock or unlock schema
• Specify field properties
• Aggregate data (only for target grid)
Adding Field Record
To add a field record for a group:
1. In the tree view, click the group record for which you want to add a field.
2. Click
.
A row is added to the grid. You can also right-click on the grid and select Add Field.
To insert a field in the middle of a field list, select the row before which you want to add the field and click
. A new field will be added before the selected row. If no row is selected, then the new field is added to the end of the field list. If all fields are selected and you insert a new field, the new field will be inserted at the end of the field list. The same behavior occurs when you right-click and select
Add Field or when you use the <Insert> key.
3. Click within the cell and specify the following information. For the Properties and Aggregating columns, use the icons.
Specifying Field Expression
To specify the field expression, in the
Expression column, click within the cell and change the value or click
to open the
Expression Editor.
Specify the expression and close the editor.
Note: The target field expression displays the = sign by default and you cannot delete this sign.
Specifying Field Properties
1. In the
Properties column, click
for the row for which you want to change the values.
Or
Right click on the row for which you want to specify the field properties, and click Properties.
Note: is read-only if the source or target schema is locked.
Note: The Properties option is grayed out if <All Fields> is selected.
The Field Properties window is displayed.
2. Specify field values that differ from the default values.
3. From the Data Type drop-down list, select the required data type.
Based on the selected data type, the properties are displayed.
4. Specify the value for the properties as required.
5. Click OK.
The changes are saved.
Changing Data Type for Multiple Fields
To change the data type for multiple fields:
1. In a map > Mapping tab, in the source or target grid, press Shift and select the fields for which you want to change the data type.
2. Right-click and select Properties.
The Field Properties window is displayed.
3. From the Data Type drop-down list, select the required data type.
For all the selected fields, the Type column displays the selected data type.
Note: You can change the data type for multiple fields within the same source or target.
When you change the data type, the Size for the selected data type changes to the default value. However, Text and Name data types does not have default values.
Aggregating Data
You can perform data aggregation for target fields, such as find an average or a sum. Values that are mapped to target fields can be calculated by assigning aggregate functions.
| This feature is not applicable when v9 Compatibility mode is enabled. |
You can add, edit, or delete aggregate functions on the Mapping tab (Simple or Advanced View) in a Map file.
Prerequisites
• Make sure that the Enable v9 compatibility mode is not selected on the Options menu > Preferences window.
• Source and Target connections are established and the source fields are mapped to the required target fields.
To add, edit, or delete aggregate function:
1. Go to any of the following:
• Simple View > Target grid
• Advanced View > Target tree > record instance > Groups > click group record
2. To add an aggregate function:
a. In the Aggregating column, click the target for which you want to add the aggregated value.
b. Click
and then click
Add Aggregation.
The Add Aggregate Function dialog box is displayed.
c. Specify the values for the following properties.
Note: Only if you select the
Aggregating option, then the
Aggregate Distinct,
Function, and
Parameters grid are enabled.
The
Aggregate Distinct is supported only for a few functions. For information about the supported functions, see
Aggregate Functions d. Click OK.
The added aggregate function is displayed in the Aggregating column as a link. If you hover on this link, then the following information is displayed:
• Function - Provides function name for the Aggregation
• Parameters - Provides values for the Aggregation
• Target Path - Provides target path for the Aggregation
• Aggregate Distinct - Indicates whether aggregate distinct is supported or not
3. To edit an aggregate function:
a. In the Aggregating column, click the aggregate function that you want to modify.
b. Click
and then click
Edit Aggregation.
The
Edit Aggregate Function is displayed.
c. Edit the required properties. For information about the fields, see the steps to add aggregation.
d. Click OK.
The updated aggregate function is displayed as a link in the Aggregating column.
4. To delete an aggregate function:
a. In the Aggregating column, click the aggregate function that you want to delete.
b. Click
and then click
Delete Aggregation.
The delete confirmation message is displayed.
c. Click Yes.
The aggregate function is deleted.
5. In the Advanced View > Source or Target tree, click the Events node for the record instance and add an event with Aggregate as the event action. For example, add the following:
– RecordStarted event with the Aggregate event action.
– SourceEnded event with OutputRecord event action.
6. Validate and run the map.
The results are displayed on the Results tab.
Aggregate Functions
Aggregate functions are target node features that perform computations on data before outputting the resulting value.
By default, the aggregate functions include all data values in a computation. When applicable, you can use the Aggregate Distinct option to eliminate duplicate values before the computation. For example, if you wanted to count the total number of job titles within a company with each job title named only once.
The following table provides the list of aggregate functions and the descriptions.