Joining tables in the Data Access window
Using the Data Access window, you can import up to ten tables in a single import operation.
You must join the tables in order to import them together. You cannot import multiple tables individually with one import operation.
Note
For information about joining Analytics tables once they are in Analytics, see Joining tables.
This topic is about joining tables in the Data Access window as part of the data import process.
How joining tables works
Joining tables in the Data Access window is the process of selecting up to ten tables in the source data to add them to the Staging Area, and then joining the tables two at a time until all tables are joined.
You join the first two tables using a common key field – that is, a data element such as "Customer ID" that appears in both tables. When identical values exist in the key fields, the result is a match that joins individual records from the separate tables.
If you are joining more than two tables, you join the second table to a third table using a common key field in those two tables, and so on, until all tables are joined.
Example
You are working with accounts receivable data and you want a list of customers, the orders for each customer, and the details of the products on each order.
To assemble this data and import it into Analytics, you need to join three tables from the source data system:
- Join #1 you join the Customer and Orders tables using the key field of CustID, which appears in both tables
- Join #2 you join the Orders and Product tables using the key field of ProdID, which appears in both tables
In the figure below, only join #1 has been completed, so the second join icon is still red.
Tip
You can try this example join yourself in the Data Access window. Use the Microsoft Access connector and connect to this sample Microsoft Access file that comes with Analytics:
..\ACL Data\Sample Data Files\Sample.mdb.
Join types
When you join tables you can choose from among four different join types. The join type you choose controls which records from the two original tables are included in the joined table.
Left and right tables
The two original tables are identified as "Left" and "Right" based on the order in which you select them:
- Left table the first table you add to the Staging Area
- Right table the second table you add to the Staging Area
Joining multiple tables
If you add more than two tables to the Staging Area, the left table is to the left of the join icon between the two tables you are joining, and the right table is to the right.
In the example above:
- Join #1 Customer is the left table and Orders is the right table
- Join #2 Orders is the left table and Product is the right table
Notice how Orders can be either the right or the left table depending on which join we are referring to.
Records included in the joined table
You can choose to include only matched records from original tables in a joined table, or you can also include unmatched records.
| Join type | Records included in joined table | ||||
|---|---|---|---|---|---|
| Matched left table records | Unmatched left table records | Matched right table records | Unmatched right table records | ||
|
Inner |
|
|
||
|
Outer |
|
|
|
|
|
Left |
|
|
|
|
|
Right |
|
|
|
|
Joining using more than one key field
You may need to use more than one key field to join two tables if the values in a single key field are insufficiently unique to accurately join the tables.
Example
You want to join two tables by vendor ID but some of the vendors have more than one location and you want to keep the records for each location separate. To achieve this result you use both Vendor ID and Location fields as key fields.
If you use only Vendor ID as a key field, records for individual vendor locations are intermixed.
If you use only Location as a key field, records for different vendors are intermixed.
| Vendor ID | Location |
|---|---|
| A-4538 | Vancouver |
| A-4538 | Burnaby |
| A-4538 | Richmond |
| B-2204 | Vancouver |
| B-2204 | Burnaby |
Joining tables from Apache Drill data sources
Using the visual editor in the Data Access window, you can join only two tables from an Apache Drill data source.
To join more than two tables from a Drill data source you must use SQL Mode and construct a join statement that does not use parentheses.
Joins of three or more tables constructed using the visual editor place parentheses into join statements, which are not supported for imports from Drill.
