Concept: Join Tables
Joins enable a look-up on another table or bringing 2 tables with a like column,
together.
You can drag-and-drop a custom join table from the
Data Components
pane into your data source to combine multiple tables into 1 table using an SQL
expression. Creating a join requires identifying a column in a primary table to act as a
key in a secondary table. The join matches up values that coexist in the key column and
creates a new record with them. The collection of these joined records generate the join
table.Before you create a join table, see if a Workday
report, or calculated field, can be created for your data source that accomplishes the
same goal.
The type of join determines what parts of the joined tables form records in the new
table:
- Left outer joins gather all of the records in the primary table and any overlapping records in the secondary.
- Right outer joins gather all of the records in the secondary and any overlapping records in the primary.
- Inner joins only return records where the key matches in both tables.
Check for duplicates to ensure you're using the right
join type.
Your cost center hierarchy could act as your primary table. The currency tied to each
cost center could exist in your secondary table. You could join these tables using Cost
Center ID as your key. The overlap of the two matches records by Cost Center ID, letting
you pull in all currencies by cost center into a new table.
Use a column prefix to help identify where a column comes from when you create a join
table.