Performing a data join
💡 For more information, you can also watch our video tutorial How to join two data models.
It is possible to join data from several data models.
Data is combined horizontally. Columns from different models are aggregated, provided they have at least one column match, known as a join key.
- In the Create a new data model box, click on the Join button in the Other section.
➡ The Join dialog box is displayed.
- Select the data models you wish to use by clicking the Add button and then choosing Existing.
The first template selected is the master model.
You can edit the data models by clicking the Edit button
. - In the Key Columns table, the key columns identified by the software are displayed. Select the key dimension(s) on which the join will be performed. The key dimensions must exist in all the selected models.
If the dimensions have different identifiers in the data models, you can create a join key by clicking the Add a key button
- In the Join type drop-down list, select the type of join you wish to perform:
- Inner join: a join which only displays rows which have a match in the attached sources;
- Outer join (1st source): join including rows even if they do not match rows in the joined table. The join includes all rows from the first table cited, even if there are no matches in the joined sources.
- Click Next to access the Advanced configuration window.
Example: creation of a join between two tables
You want to create a join between an Excel file containing the names of customers (customers.xls) and a second file containing the sales made for these customers (sales.xls).
- Create a Customers data model from the Customers.xls file containing the following data:
| Customer ID | First name | Last name | Date of birth | Phone | |
|---|---|---|---|---|---|
| 1 | Nicolas | Martin | nicolas.martin@yahoo.fr | 04/02/68 | 01.64.98.56.39 |
| 2 | Aurélie | Richard | aurelierichard@gmail.com | 04/04/74 | 04.32.17.29.90 |
| 3 | Julien | Simon | julien.simon@free.fr | 05/24/78 | 06.75.96.56.43 |
| 4 | Emilie | Roux | emilie.roux@yahoo.fr | 10/20/80 | 02.78.76.98.45 |
| 5 | Elodie | Moreau | elodie.moreau@gmail.com | 04/11/69 | 09.89.75.78.76 |
- Create a Sales data model from the Sales.xls file containing the following data:
| Customer ID | Date | Price Total |
|---|---|---|
| 2 | 06/05/04 | 100,22 |
| 1 | 07/05/04 | 99,95 |
| 3 | 07/05/04 | 122,95 |
| 3 | 05/13/04 | 100,22 |
| 4 | 05/22/04 | 555,55 |
| 6 | 07/29/09 | 50 |
- Create a new Join data model.
- Add the Sales data model, then the Customers data model.
- Check the Customer ID key column, then select the Join type.
- Selecting the Inner Join type
If you create a table based on this new data model, you will obtain the following result:
| Customer ID | First name | Last name | Date | Sales |
|---|---|---|---|---|
| 1 | Nicolas | Martin | 05/07/2004 | 99,95 |
| 2 | Aurélie | Richard | 05/06/2004 | 100,22 |
| 3 | Julien | Simon | 05/07/2004 | 122,95 |
| 3 | Julien | Simon | 13/05/2004 | 100,22 |
| 4 | Emilie | Roux | 22/05/2004 | 555,55 |
The sale made for customer 6 is not referenced because there is no corresponding row in the Customer table.
- Selecting the Outer join (1st source) type
If you create a table based on this new data model, you will obtain the following result:
| Customer ID | First name | Last name | Date | Sales |
|---|---|---|---|---|
| 1 | Nicolas | Martin | 05/07/2004 | 99,95 |
| 2 | Aurélie | Richard | 05/06/2004 | 100,22 |
| 3 | Julien | Simon | 05/07/2004 | 122,95 |
| 3 | Julien | Simon | 13/05/2004 | 100,22 |
| 4 | Emilie | Roux | 22/05/2004 | 555,55 |
| 6 | 29/07/2009 | 50 |
The sale made for customer 6 is referenced this time (the result includes all the lines in the Sales table, even those whose customer ID is not in the Customers file).