Creating Join Definitions manually - 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
Use the Joins builder to combine data from columns of two physical model tables before it is populated to the logical model columns.

To create a join definition, perform these steps:

  1. On the Create Mapping <logical model name> page, select the required logical entity, and click the Join builder icon on the tool bar . Alternatively, click the Entity Transformation icon of the required logical entity and use the forward arrow on the top right of the page to go to the Join Definition for logical table <table name> page.
  2. On the Join Definition tab, 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 to specify the columns 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.
    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 columns on the basis of the defined join.
    Note: Joins are not represented by any physical link between the tables on the canvas.
  10. To delete any join, hover the required row, and click the Delete icon that appears.