Working with join transformations

You use join transformations to join result set columns from different queries and tables.

1. In the Analytical query structure, select a join transformation node.
   In the Item editor pane, you can view the items of the join transformation. The columns from the source data set in the upper node are displayed in the Left Columns list. The columns from the source data set in the lower node are displayed in the Right Columns list.
2. Optional: If the selected join transformation is not populated yet, add queries or tables into <none> nodes. For more information, see Populating analytical query transformations.
3. On the toolbar in the Item editor, select the type of join.
   • Inner join to include in the query results only those rows with matching values in both tables.
   • Left outer join to include in the query results all the rows in the left table and only the rows from the right table that match with rows from the left table.
   • Right outer join to include in the query results all the rows in the right table and only the rows from the left table that match with rows from the right table.
   • Full outer join to include in the query results all rows from both tables.
4. Specify the query result columns that you want to join together.
   1. Select a column from the Left Columns list.
   2. Select a column from the Right Columns list.
   3. From the Join Columns list, select the check box next to the join condition.
      The new join condition is added to the Join Columns list and the Condition(s) Added check box is selected.
   4. Repeat this procedure for each join condition that you want to add. For each new join condition, you can only use columns that were not previously selected.
   5. To clear the Join Columns list, deselect the Condition(s) Added check box.
5. If you are populating a join transformation structure with objects that contain similar names, you can try to automatically create appropriate join conditions by clicking Automatch.
   If there are any possible automatches, they are displayed in the Join Columns list.

   Note: Clicking Automatch will erase any join conditions that you previously set.
6. Optional: You can configure the join condition to be applied in case sensitive or case insensitive manner by clicking on the Collate Option button. Specify Collate Option for each join column by selecting case sensitive or case insensitive option from the drop-down provided.
   Note: Collate Option button is enabled when at least one of the join column is of the character data type.
7. To add a column to the Result Columns list, select the check box next to this column.
8. To move a column up or down the Result Columns list, select the column and click Move Column Up or Move Column Down buttons above the Result Columns list.
9. To add all of the Left Columns or Right Columns to the Result Columns list, select the Include All Columns check box above the respective column list.
10. To remove all of the Left Columns or Right Columns from the Result Columns, deselect the Include All Columns check box above the respective column list.
11. Optional: To add another transformation to the current analytical query structure, follow the procedure described in Building analytical query structures.