User Guide : Designing and Executing Map : Mapping Target from Source Data : Managing Field Records in Advanced View
 
Share this page             
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 /download/attachments/24975466/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487964384147&api=v2.
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 /download/attachments/24975466/Map_Mapping_AddField_Icon.png?version=1&modificationDate=1487964384147&api=v2. 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.
 
Column Name
Description
Field
Name of the field.
Expression
For information, see Specifying Field Expression
Note:  This column is applicable for targets only.
Description
Description for the field.
Type
Data type of the field.
Size
Size of the field.
Properties
Aggregating
For information, see Managing Events and Actions.
Note:  This column is applicable for targets only.
Specifying Field Expression
To specify the field expression, in the Expression column, click within the cell and change the value or click /download/attachments/24975466/SelectIcon_WithinField.png?version=1&modificationDate=1490090669113&api=v2 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 /download/attachments/24975466/Map_Mapping_PropertiesIcon.png?version=1&modificationDate=1487964384271&api=v2 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:  /download/attachments/24975466/Map_Mapping_PropertiesIcon.png?version=1&modificationDate=1487964384271&api=v2 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.
Property
Description
Aggregating
Indicates that the value will be aggregated for the selected target.
By default, this option is selected.
Aggregate Distinct
Eliminates duplicate values before the computation. By default, this option is not selected.
Target Path
Displays the target path in the following format:
/<<TargetName>>/<<RecordName>>/<<FieldName>>
Function
Select the required function. By default, Average is selected. For more information about functions, see the Aggregate Functions table.
Parameters grid with Parameters and Values columns
Based on the selected Aggregate Function, specify the value for the parameter. For example, if Concat is the selected Function, then you must provide the Value for the value_separator parameter.
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.
For information to add events and event actions, see Managing Events and Actions.
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.
Aggregate Function
Distinct Values Supported
Description
Average
Yes
Returns the average value of the set of numeric values computed by the expression each time the target field is mapped. Non-numeric data values are ignored.
Concat
Yes
Returns a string of concatenated values from the mapping expression.
Count
Yes
Returns the number of non-NULL values for the set of values computed by the expression. If the expression is omitted, the result is the count of how many times the Count function is evaluated.
When you use the Count function on dates, it counts the number of valid dates in a column and returns that number.
Sum
Yes
Returns the sum of the set of numeric values computed by the expression each time the target field is mapped. Non-numeric data values are ignored.
First
No
Returns the first value computed by the expression.
Last
No
Returns the last value computed by the expression.
Max
No
Returns the maximum value of the set of values computed by the expression each time the target field is mapped.
Min
No
Returns the minimum value of the set of values computed by the expression each time the target field is mapped.