Creating Complex Joins
You can join tables that have different column names for the same information in different sources. For example, one table might have a column called ShipVia but the same column in another table is called ShipperID. To join these two tables, you can create a complex join.
Syntax Guidelines:
- When referencing a table in the data store (warehouse), include a dollar sign ($) at the end of the physical column name (the only exceptions are SCD_END_DATE, SCD_START_DATE, and LOAD_ID). To determine the physical column name, convert the logical column name by replacing any spaces or punctuation with an underscore and adding a $. For example, ShipperID becomes ShipperID$ and First Name - Customer becomes First_Name___Customer$.
- When there is a dollar sign ($) in a physical column name, convert it to the capital letter D. For example, $ Sales - Gross becomes D_Sales___Gross$.
- When there is a hash sign (#) in a physical column name, convert it to the capital letter N. For example, # Users becomes N_Users$.
- The logical table name of an inherited source is the name of the inherited source (the name that was entered when it was created).
- To see the logical and physical table names for a dimension or measure table from
either the complex join or compound table interface:
- When using complex joins, the logical table name will be pre-filled in the join condition
- When using the compound table interface, the logical and physical table names can be found in the interface (logical being the top name, physical the bottom one).
Note: Users can copy the physical table name by hovering over the
table and selecting from the tooltip.
![](wsq1655272581218.png)