XLSTAT: Merging Tables

 

Background

Merging (joining) is a common task in database management. It allows to combine rows from two tables on the basis of a common information named the key.

There are two main types of joins:

• Inner join: the merged table includes only keys that are common to both input tables.

• Outer join: the merged table includes all keys that are available in the first, the second or both input tables.

 

Dataset for Example

The data set consists of two tables. In the first one, each row represents a product item for an order. For example, order 101 has three items, order 102 has 1 item, etc. The invoice amount as well as the product id are provided for each item. The second table contains information on the store where each order was placed. For example, order 101 took place in store A.

Here, we want to integrate the Store information in the first table. For this aim, we will show how to use both types of merging (inner and outer) and observe the differences between the output tables.

Setting Up

• Open XLSTAT.
• Click on the Preparing data menu and select the Data Management feature:

 

• The Data management dialog box appears. 

• Select cells A1-C12 from Sheet1 in the field Table 1 and cells E1-F7 in the field Table 2.

• In the Method field, select Join (Inner).

• Headers are included in our data selection, so we also need to check the Variable labels.

• Click on the OK button.

• A new window appears to select the common key.
• In this window, it is possible to select for the join. In the case where several keys are selected, for the analysis to work, the keys must be put in the same order in each of the two tables, otherwise, an error message will be displayed.
• Select the Order ID as the common key:

• Click on the OK button.
• Once the XLSTAT report is generated, repeat the procedure using the Join (Outer) option in the first dialog box.

Interpreting the Results

Results from an Inner Join of Two Tables

The merged table below includes only orders that are common to both input tables. Order 107 was excluded as Table 2 does not provide the Store details for this order.

Results from an Outer Join of Two Tables

The merged table below includes all orders from both input tables. The Store column contains a missing value for order 107 since there is no information available in Table 2 for this order.