Creating a Join - discovery - 23.1

Spectrum Discovery Guide

Product type
Product family
Spectrum > Discovery
Product name
Spectrum Discovery
Spectrum Discovery Guide
Topic type
How Do I
First publish date
  1. On the Create Mapping <logical model name> page, select the required logical entity, and click the Join builder icon on the tool bar .
    Note: The Transformation panel doesn't display details of the physical model table. In case you click a physical model table after an entity and open the Transformation panel, it displays details of the last selected entity.
    The Join Builder for logical table <name of the table> pop-up window is displayed.
  2. Click the Add table button.
    A table is displayed with Left table, Right table, and Join type columns.
  3. Use the <select table> and <select column> drop-down options of the Left table and Right table columns to specify the columns (of the physical model tables) from which data is to be joined.
  4. From the Join type drop-down list, select the type of join you want between the columns. The options are:
    • Inner join: Returns matching records between the selected left and the right table columns.
    • Left join: Returns all records from the left column, in addition to any matching records from the right column.
    • Right join: Returns all the records from the right column, in addition to any matching record from the left column.
    • Full outer join: Returns all the records from the left and the right columns.
  5. To establish joins on multiple columns of the table, hover cursor over the table row, and click the Add comparison icon that appears.
    A new <select column> row is displayed.
  6. Repeat steps 4 and 5 to define the join criteria.
  7. To establish join between another set of tables, click the Add table button.
    Note: You need to have the set of table dragged on to the canvas to be able to establish join between those.
    A new set of <Select table> and <Select column> rows are displayed.
  8. Repeat steps 3 and 4 to define the join.
  9. Click OK.
The join between the tables is established and data is populated in the logical model column from the selected physical model columns on the basis of the defined join.
Note: Joins are not represented by any physical link between the tables on the canvas.