Mapping Specification Joins
When you use more than one source in a mapping specification, you can select a join type between sources and specify the join criteria. The Analyst tool recommends join conditions for a join between sources. You can also select other join conditions.
After you create and name a join, you can use this join object to join between another join object. You can edit a join to modify the join condition that you specify for a join.
Add a join to add multiple sources to a mapping specification. Edit a join to modify the join condition between the sources.
You can add the following types of join conditions:
- Simple join
- Uses a single operator in a join condition.
- Advanced join
- Uses expression functions in the join condition.
When you join between sources, you join the columns from the detail table and the master table.
The following table describes the type of joins:
Join Type | Description |
|---|
Normal | Discards all rows of data from the master and detail source that do not match, based on the condition. |
Master Outer | Keeps all rows of data from the detail source and the matching rows from the master source. Discards the unmatched rows from the master source. |
Detail Outer | Keeps all rows of data from the master source and the matching rows from the detail source. Discards the unmatched rows from the detail source. |
Full Outer | Keeps all rows of data from both the master and detail sources. |
Note: When you create a join between two or more relational tables that have the same column or table names, the Analyst tool uses the table name and column name to create a unique name. For example: TableName.ColumnName.
Adding and Editing a Simple Join
Add a simple join condition to use a single operator in a join condition. Edit a simple join to modify the join condition between sources.
1. From the Actions menu, click Edit > Joins.
The Edit Mapping Specification wizard appears.
2. On the Join panel, select one of the following choices:
- - To add a join, click the New icon.
- - To edit a join, select the join and click the Edit icon.
The New Join or Edit Join wizard appears.
3. Enter a name and an optional description.
4. Select a join type from the Join Type field.
5. Select a master data object from the Master field.
6. Select a detail data object from the Detail field.
7. Select Simple Join.
8. Choose to add edit a join condition on the Join Condition panel.
- - To add a join condition, click the New icon and select a Master Column Name, Operator, and Detail Column Name.
- - To edit a join condition, modify the Master Column Name, Operator, and Detail Column Name.
9. Optionally, click the Validate icon to validate the join condition.
10. Optionally, click the Refresh icon to preview the data.
11. Click OK.
Adding an Editing an Advanced Join
Add an advanced join to use expression functions in the join condition. Edit a simple join to modify the expression in the Expression editor.
1. From the Actions menu, click Edit > Joins.
The Edit Mapping Specification wizard appears.
2. On the Join panel, select one of the following choices:
- - To add a join, click the New icon.
- - To edit a join, select the join and click theEdit icon.
The New Join or Edit Join wizard appears.
3. Enter a name and an optional description.
4. Select a join type from the Join Type field.
5. Select a master data object from the Master field.
6. Select a detail data object from the Detail field.
7. Select Advanced Join.
8. On the Functions tab, select a function, click the arrow icon, select a column, and click OK to add the function to the Expression editor.
9. Optionally, on the Master Columns tab, select a column and click the arrow icon to add it as a master column to the Expression editor.
10. Optionally, on the Detail Columns tab, select a column and click the arrow icon to add it as a detail column to the Expression editor.
11. Optionally, click the Validate icon to validate the expression.
12. Optionally, on the Data Preview panel, click the Refresh icon to preview the data.
13. Click OK.