Joining Data

About Joining Data

Use a join to create a new sheet containing columns from two or more sheets based on a key column that you define. 

Like all worksheets in a workbook, a joined worksheet is based on sample data defined from the source of the data. When first creating a joined worksheet, the data displayed 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, Datameer informs you 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.

Creating a Join

To create a join:

  1. Either click on the Join icon on the toolbar or select Join from the Data menu to open the Join window.
  2. Select the type of join you want to create. See Types of Joins to learn more.
  3. Select the sheet and columns you want to join from the menu on the left and drag them to the appropriate fields.
  4. Select ShowIncludeColumns and selected the columns you want to have displayed.
  5. Click CreateJoinedSheet.

Joined Sheet Naming

If you create 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. For example, "Name" becomes "Name_MarketingData". This new name is stored immediately and not updated if the column name in the source sheet changes. Existing sheets (prior to 6.3) do not see this name change unless the parameters for the join are edited and a previous excluded column is included. 

Creating a Join From Multiple Columns

Creating a multi join or multi-key join is easy with Datameer. With the multi-key join, you can specify multiple columns from two different worksheets and join the data together.

  1. Add the data sources that need to be joined together in a workbook.

  2. Select Join from Data menu or click the Join icon on the toolbar.
  3. Choose the + (plus) button in order to add additional column keys to the join. 
    The same data source must be used in the left and right columns as the previous data sources. If you want to join data from multiple sheets, use the + (plus) button at the bottom of the page to create joins from multiple data sources.
  4. Select Chose included columns to select the columns to include on the join sheet.
  5. Click Create Joined Sheet to view the results.

Example

The columns from the sheets being joined must have a common key that links the information together.

Sheet 1

ScreenNamePosts
Jeff54
Mike36

Sheet 2

ScreenNameTimeZone
JeffEast Coast
MikeWest Coast

Create an inner join by screen name. Deselect Sheet 2.ScreenName under Show Include Columns.

Sheet 1.ScreenNameSheet 1.PostsSheet 2.TimeZone
Jeff54East Coast
Mike36West Coast

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. 

Additional join columns can be added to the worksheet by clicking the + (plus) icon. The Hide Included Columns section allows you to remove columns from being displayed on the completed join worksheet.

Example

The columns from the sheets being joined must have a common key that links the information together.

Sheet 1

UserTime
Jeff17/10/2015
Mike18/10/2015

Sheet 2

ValueTime
4534518/10/2015
9238319/10/2015

Create a range join by Sheet 1.Time is less than Sheet 2.Time. Deselect Sheet 2.Time under Show Include Columns.

Sheet 1.UserSheet 1.TimeSheet 2.Value
Jeff17/10/201545345
Jeff17/10/201592383
Mike18/10/201592383

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.

  1. Add the data source in a workbook.
  2. In a worksheet, select Edit > Join... or click the Join icon.
  3. 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.
  4. The result is a self join using the employee names as a key and displaying matching records.