Matching fields page

Application database > Analytical Workspaces tile > workspace tile > (Data Transformation Package tab) DTP tile > > Operations > (Operation type) Matching fields

Application database > Navigation panel > Analytic Information Hub > workspace tile > (Data transformation package tab) DTP tile > > Operations > (Operation type) Fields mapping

Purpose of the page

This page allows you to define correspondence between the fields of a source dataset and a target dataset, in order to guide the operation to insert or enrich fields of the target dataset.

For the Enrichment with Reference dataset operation, the window is accessed via the Advanced settings link.

For the Delete Multiple Keys/Check Multiple Keys operations the mask can be reached via the link Key Fields.

Page sections

Element	Description
List of relationships	Shows the list of relationships between fields of the source dataset and fields of the target dataset.
Attributes	Allows you to define the relationship properties, to pilot the operation.
Operation information	Shows contextual information on the operation: - Code: code of the operation for which the matching fields are been modified. - Description: operation description - Type: operation type

Relationship definition

Element	Description
Target field	With operations Enrichment from other dataset, Enrichment with formula, Enrichment with ranking, Join with reference dataset, Enrichment with reference dataset, Join from other dataset, Check missing keys, Delete missing keys, Insert with unpivot transposition, Insert with pivot transposition, Delete multiple keys, Check multiple keys - allow you to specify the field of the target dataset. With operations Insert from other dataset, Insert with unpivot transposition, Insert with pivot transposition, the system allows you to specify special values: - Relate remaining fields automatically: - matches by name any fields not defined by the matching fields. - Apply formula to the remaining fields: - applies the formula defined in the source field to any field not defined by other matching fields. Note: the two special options cannot both be saved for the same operation.
Condition	With Enrichment with formula operations: - allows you to specify the condition to be met in order to apply the SQL formula or the alternative SQL formula.
SQL formula	With Enrichment with formula operations: - allows the source value of the enrichment to be identified by means of an SQL formula, or by entering the code of the source field. It is possible to refer fields of the target dataset and the source dataset, via the aliases #DEST# and #SOURCE# For example, it is possible to write a formula as follows: CASE WHEN #DEST#.CAMPOX THEN #SOURCE#.CAMPOY ELSE #SOURCE#.CAMPOZ END so that you can customise a condition type matching field; in such a situation, it becomes mandatory to specify the datasets to which all matching fields defined via aliases belong, even in the case of a source formula with only one field. N.b. CAMPOX, CAMPOY, CAMPOZ indicate, by way of example, the dataset fields involved
Alternative SQL formula	With Enrichment with formula operations: - allows you to enter an SQL formula to be applied when the condition to apply the formula is not met.
Operator	With Reference dataset enrichment operations, allows you to select the operator to be applied in the comparison with the reference dataset. - > - => - < - <=
Source	With operations Insert from other dataset, Enrich from other dataset, Insert with formula, Enrichment with reference dataset, Join from other dataset, Check missing keys, Delete missing keys, Insert with unpivot transposition, Insert with pivot transposition: - allows you to indicate the field in the source dataset from which the data is taken for enrichment. It can be defined with the dataset field code or an SQL formula to calculate its value.
Pivot value	With Insert with unpivot transposition operations: - allows you to indicate the field in the source dataset from which you will take the data for transposition that will be inserted as data in the pivot value target field specified on the operations page. It can be defined with the dataset field code or an SQL formula to calculate its value.
Pivot aliases	With Insert with unpivot transposition operations: - allows you to indicate the alias to be used as data in the Pivot attribute target field specified on the operations page.
Relationship type	With Insert from other dataset, Join from other dataset operations : - Condition indicates a join field. Condition fields allow you to identify reading records for every journal record. With Insert from other dataset, Join from other dataset, Insert with Unpivot transposition operations: - Attribute indicates that it is a field to be defined. With Insertion with Unpivot transposition operations: - Pivot indicates the field to be transposed
Grouping mode	Defines the aggregation function to be applied to the source fieldwhen reading data: - With Relationship set to Condition, the only permitted value is GROUP BY. - With Relationship set to Attribute, one of the following aggregation functions must be specified: SUM, MIN, MAX or COUNT. With Enrichment from other dataset operations: - it is not possible to use aggregation functions if an alias was used to refer to the target dataset or the source dataset. With operations Insert from other dataset, Join from other dataset, Insert with pivot transposition: - these restrictions do not apply. With Insert with unpivot transposition operations: - such restrictions do not apply. The only existing restriction is that when Relationship is set to Pivot, the GROUP BY value cannot be used.

Actions on this page

Element	Description
Export for Import	Runs the download of field relationship data in XLSX format.
Import from Excel	Opens a window to select an XLSX file from which to import data into the field relationships.