DC 12.0 | Managing Field Records in Advanced View

User Guide > Designing and Executing Map > Mapping Target from Source Data > Managing Field Records in Advanced View

Was this helpful?

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.

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	For information, see Specifying Field Properties and Changing 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

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

You can specify the properties of a field using the Field Properties dialog. The dialog can be opened in multiple ways:

1. In Advanced view, do any of the following:

• Click the Properties icon (

) in the Properties column of the source or target layout grid.

• Right-click in the source or target layout grid, then select Properties from the context menu.

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 dialog is displayed.

2. Specify the value of any field property or change the field data type:

Properties

You can specify the following properties for a field:

• Field Required - Select Yes if the field is required, else select No. Default is No.

• Description - Description of the field.

• SQL Literal - Select Yes to insert the value of the field into a SQL statement without quotations (""), else select No. Default is no.

• Min occurrence - Specifies the minimum number of times that the field(s) can occur in a given record. Select one of the following:

– 0

– 1

– 2

• Max occurrence - Specifies the maximum number of times that the field(s) can occur in a given record. Select one of the following:

– 0

– 1

– 2

– Unbounded

• Default Value - If applicable, enter the default value. Default values can be applied to target field expressions.

• Data Type - Select the data type from the dropdown menu. Based on the selected data type, relevant properties are displayed. You must be connected to see the complete data type list.

3. Click OK.

The changes are saved.

Changing Field Properties

You can change the properties of a single field, or multiple fields at once, using the Field Properties dialog. The dialog can be opened in multiple ways:

1. In Advanced view, do any of the following:

• Click the Properties icon (

) in the Properties column of the source or target layout grid.

• Right-click in the source or target layout grid, then select Properties from the context menu.

The Field Properties dialog is displayed.

2. Change the value of any field property or change the field data type.

3. Click OK.

The changes are saved.

To edit the field properties of multiple fields at the same time, use the Shift or Control key to select multiple fields in the source or target layout, then open the Field Properties dialog using one of the methods mentioned above. Any changes made in the dialog will be propagated to all of the selected fields.

Note: If the selected fields have the same data type, the Field Properties dialog will contain the data type and associated properties. If the selected fields do not have the same data type, the dialog will be blank. In this scenario, select the data type you would like to apply to the selected fields first. Once the data type has been selected, the properties will appear in the Field Properties dialog and can be edited.

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.

You can add, edit, or delete aggregate functions on the Mapping tab (Simple or Advanced View) in a Map file.

Prerequisites

• 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.

Last modified date: 02/01/2024