Joining Data
Joining data allows to create a new Workbook sheet, containing columns from two or more sheets based on a pre-defined key column.
A joined worksheet is based on sample data defined from the data source. When first creating a joined worksheet, the displayed data is based on the current sampling of data. After the Workbook has been run, the joined sheet calculations are applied to the full data. When creating a joined sheet that displays no data, the UI informs you in an info message that the empty sheet is displayed based on the current sample data. Running the Workbook again creates. a new sampling for the sheets that take into account the calculation of the joined sheet.
Types of Joins#
Inner Join
Inner Join#
An inner join returns only the set of records that are contained in both Sheet A and Sheet B.
Full Outer Join
Full Outer Join#
A full outer join returns the set of all records in Sheet A and Sheet B, with matching records from both sides where available. If there aren't any matches, the affected records are paired with a null value.
Outer Left Join
Outer Left Join#
An outer left join returns a complete set of records from the left sheet, Sheet A, with the matching records (where available) in Sheet B. If there aren't any matches, the affected records are paired with a null value.
Outer Right Join
Outer Right Join#
An outer right join returns a complete set of records from the right sheet, Sheet B, with the matching records (where available) in Sheet A. If there aren't any matches, the affected records are paired with a null value.
Self Join
Self Join#
A self join combines a sheet with itself returning related records from the same sheet. Self joins are useful when one record falls into two categories.
Example: A list of employees would show that John is an analyst, but also that he is head of the department. With a self join you could show that he belongs to both groups.
Creating a Join#
The columns from the sheets being joined must have a common key that links the information together.
Simple join Example
Sheet 1 contains:
| ScreenName | Posts |
|---|---|
| Jeff | 54 |
| Mike | 36 |
Sheet 2 contains:
| ScreenName | TimeZone |
|---|---|
| Jeff | East Coast |
| Mike | West Coast |
Create an inner join by 'screen name' and get the result:
| Sheet 1.ScreenName | Sheet 1.Posts | Sheet 2.TimeZone |
|---|---|---|
| Jeff | 54 | East Coast |
| Mike | 36 | West Coast |
To create a join:
-
Open a Workbook and click on the "Join" icon or click on "Edit" → "Join". The 'Create a Joined Sheet' dialog opens.
or
-
Select the required type of join from the drop-down within the 'Simple' tab.
-
Unfold the Workbook sheet and column menu and position the required columns via drag and drop on the appropriate fields.
-
Select "Included columns" to display the content and select the columns you want to have displayed in the result join sheet.
-
Confirm with "Create Joined Sheet". The new joined sheet appears in the Workbook. Joining Data is finished.
or
Creating a Join From Multiple Columns#
With the multi-key join, you can specify multiple columns from two different worksheets and join the data together.
To create a join from multiple columns:
-
Open a Workbook and add the data sources that need to be joined together in a Workbook in different sheets.
-
Click on the "Join" icon or click on "Edit" → "Join". The 'Create a Joined Sheet' dialog opens.
or
-
Unfold the Workbook sheet and column menu and position the required columns via drag and drop on the appropriate fields.
-
Click the "+" button to add additional column keys to the join.
INFO: The same data source must be used in the left and right columns as the previous data sources.
INFO: If you want to join data from multiple sheets, use the "+" button at the bottom right side of the tab 'Simple' to create pins from multiple data sources.
-
Select "Included columns" to display the content and select the columns you want to have displayed in the result join sheet.
-
Confirm with "Create Joined Sheet". The result is displayed in the joined Workbook. Multiple joining is finished.
Creating a Ranged Join#
A ranged join is similar to a simple join but has additional versatility. Instead of joining worksheets using an exact matching key column, you can specify a range (greater than, less than) between two worksheet key columns.
Data types allowed by a range join are limited to dates and geolocations, e.g.
Example of a Ranged join
Sheet 1
| User | Time |
|---|---|
| Jeff | 17/10/2015 |
| Mike | 17/10/2015 |
Sheet 2
| Value | Time |
|---|---|
| 45345 | 18/10/2015 |
| 92383 | 19/10/2015 |
Create a range join by Sheet 1.Time is less than Sheet 2.Time. Deselect Sheet 2.Time under "Included Columns".
| Sheet1.User | Sheet1.Time | Sheet2.Value |
|---|---|---|
| Jeff | 17/10/2015 | 45345 |
| Jeff | 17/10/2015 | 92383 |
| Mike | 18/10/2015 | 92383 |
To create a ranged join click on the tab "Range Join" and then the "+" button to add additional columns.
Creating a Self Join#
A self join combines a sheet with itself returning related records from the same sheet. Self joins are useful when one record falls into two or more categories.
To create a self join:
-
Open a Workbook and add the data source.
-
Select "Edit" → Join or click the "Join" icon.
-
In this example of a self join, the goal is to use the employee names as a key to find if any employee has multiple roles. To do this, add the employee column.
-
The result is a self join using the employee names as a key and displaying matching records.
Joined Sheet Naming#
When creating a join sheet in a Workbook, the columns in the join sheet keep their original names unless there is an existing column with the same name.
In that case, the new column has a suffix of the sheet name. Example: "Name" changes to "Name_MarketingData". This new name is stored immediately and not updated when the column name in the source sheet changes.
Avoiding Errors When Using Other Functions After Joining Data#
When joining data and applying other Spectrum functions such as "Unioing Data" afterwards, make sure to enable column transpose when joining data. Key columns must be displayed first.
To enable transposing columns within joining data, make sure the check box "Display key columns first" is checked. This should be checked by default. If not, check it.
