As stated in the Column matches section, the purpose of data table column matching is to enable the use of multiple data tables in a single visualization. In that section, we also demonstrated how to create column matches between data tables.
Using multiple data tables in one visualization is similar to using just a single data table, although in the first we will have the concept of additional data tables besides the already known main data table:
- Main data table: It has the standard main data table behavior, defining the rows of an unaggregated visualization
- Additional data tables: These are viewed as extra columns, which can be added to visualizations. These columns can only be used in aggregation axes (y axis), not grouping (or categorical) axes (x axis).
As an example of this feature, using the main data table Employees and additional data table JobHistory, we will represent the total number of employees that are or have been in each department. We will create a bar chart with the list of departments (DEPARTMENT_ID) as x axis, and the count of employees as y axis. This count will be retrieved from both the data tables—the main and the additional.
Please be aware that chosen grouping columns (x axis) should belong to both the main and the additional data tables (they have to be matched columns) in order for the aggregation to work. Due to this limitation and because we want to use DEPARTMENT_ID as x axis, we must first match this column of data table Employees with the matching column DEPARTMENT_ID of data table JobHistory.
As specified earlier, column matching is done using the data table Properties editor from the Edit menu. In the editing tool, matching of columns can be done in the Column Matches tab.
To add a variable to a chart from a data table other than the main one, users have a drop-down list available in the variable selection pop up. Only data tables that have matched columns to the main data table will be listed in this dropdown.
The following screenshot presents an example of this pop up:
This example belongs to a visualization with the main data table Employees. Since this data table has matched columns solely in the data table JobHistory, only these two are listed.
To create an example graph for multiple data tables, open your analysis project and execute the following steps:
- Create a new page named Employees - Multiple DT.
- Add a bar chart to the new page.
- Define Employees as the chart main data table.
- Select DEPARTMENT_ID as the x axis variable of the chart.
- Select Count(DEPARTMENT_ID) as the y axis variable of the chart.
- Select Count(DEPARTMENT_ID) from the data table JobHistory as a second variable of the y axis of the chart.
The resulting graph will look similar to the one presented in the following screenshot:
We can see only departments with IDs 20, 50, 60, 80, 90 and 110 had former employees in the past (the coloring of data is different for records for each of the underlying data tables).
Save the analysis project.