Merging Tables

Application Scenarios

Merging tables is to merge several different but associated tables into one large table.

There are two merging types — Union All and Join.

Union All merges rows from different tables, as shown in the following figure.

Join merges columns from different tables, as shown in the following figure.

Join includes four modes — Left Join (retaining all the data from the left table), Right Join (retaining all the data from the right table), Full Join (retaining all the data from both tables), and Inner Join (retaining the data that has matching values in both tables).

Implementation Method

In FineBI, columns/fields with the same name are merged into one by default.

This document introduces the effects, procedures, and check items of Union All and Join.

Preview

The effect of Union All is shown in the following figure.

Function Entry

Click Union All on the data processing page to merge rows from different tables.

Procedures

For details, see Proportion Analysis for Multiple Indicators.

Sample data: Student Scores.zip 

Create an analysis subject and upload the sample data. Select Student Scores (Sheet 1) and merge it with Student Scores (Sheet 2) through Union All.

You can choose multiple tables in batch to be merged.

For example, you can merge three Excel tables at one time.

Columns/fields with the same name are merged into one by default. For consistent columns/fields with different names, you need to manually choose the fields to be merged.

Take the following figure as an example, the names of the columns referring to students' names from two tables are different. The one in Student Scores (Sheet 1) is Name2 and that in Student Scores (Sheet 2) is Name1.

Modify the merge fields of both tables to merge Name2 of the current table and Name1 of the table Student Scores (Sheet 2) into one, and rename the merged result to Name.

Preview

Join involves two tables. The left table is the one previewed on the data processing page and the right table is the one selected in the pop-up window after you click Join.

Different Join modes present different joining effects. For details, see section "Description of Join".

You need to set a Merge Basis for Join, such as the Name field in the above figure.

Description of Join

Left Join

• Left Join: joins all data from the left table and the data in the right table that has matching values in the left table.

• Function entry: Join > Left Join.

• Implementation effect: Left Join retains all data from the left table and the data in the right table that has matching values in the left table, and deletes the rest.

• Description: The left table is the one previewed on the data processing page and the right table is the one selected in the pop-up window after you click Join. By default, the Merge Basis is the field in both tables. Values corresponding to the Merge Basis in both tables are joined together. For consistent fields with different names, you need to manually choose the fields to be merged and modify the Merge Basis. For consistent fields that do not need to be merged, you need to manually delete them.

Right Join

• Right Join: joins all data from the right table and the data in the left table that has matching values in the right table.

• Function entry: Join > Right Join.

• Implementation effect: Right Join retains all data from the right table and the data in the left table that has matching values in the right table, and deletes the rest.

• Description: The left table is the one previewed on the data processing page and the right table is the one selected in the pop-up window after you click Join. By default, the Merge Basis is the field in both tables. Values corresponding to the Merge Basis in both tables are joined together. For consistent fields with different names, you need to manually choose the fields to be merged and modify the Merge Basis. For consistent fields that do not need to be merged, you need to manually delete them.

Full Join

• Full Join: joins all data from both tables.

• Function entry: Join > Full Join.

• Implementation effect: Full Join retains all data from both tables, regardless of whether the data has matching values in the other table.

• Description: The left table is the one previewed on the data processing page and the right table is the one selected in the pop-up window after you click Join. By default, the Merge Basis is the field in both tables. Values corresponding to the Merge Basis in both tables are joined together. For consistent fields with different names, you need to manually choose the fields to be merged and modify the Merge Basis. For consistent fields that do not need to be merged, you need to manually delete them.

Inner Join

• Inner Join: joins the data that has matching values in both tables.

• Function entry: Join > Inner Join.

• Implementation effect: Inner Join retains the data that has matching values in both tables and deletes the rest.

• Description: The left table is the one previewed on the data processing page and the right table is the one selected in the pop-up window after you click Join. By default, the Merge Basis is the field in both tables. Values corresponding to the Merge Basis in both tables are joined together. For consistent fields with different names, you need to manually choose the fields to be merged and modify the Merge Basis. For consistent fields that do not need to be merged, you need to manually delete them.

Function Entry

Click Join on the data processing page to join another table.

Procedures

Sample data: Subject Scores.zip

The Math Scores table contains the math scores of students A, B, and D. The English Scores table contains the math scores of students A, B, and C. Join the two tables into one to include the scores of all students. To meet this requirement, set the Join mode to Full Join.

For details, see Basic Functions of Join.

To join more tables, you can click Join again to add another table.

If the system fails to automatch the Merge Basis, you need to add it manually.

Duplicate Data after Merging

Problem:

There are duplicate values after merging.

For example, to calculate the consumption amount of users in each city, you can join the tables User Info and Order Info. After joining, there are three duplicate values of the User ID 20231011.

Cause:

Each user has multiple delivery addresses, so the User ID field corresponds to multiple values in the User Info table.

In this case, taking User ID as the Merge Basis (Primary key) leads to duplicate values.

Solution:

To retain only one piece of User 20231011's data, you can use the Group Summary to calculate the average value of Order Amount.

Over-limit Data after Merging

Problem:

Problem 1: After data update, an error message occurs on the data preview page, indicating that the current data volume is too large.

Problem 2: When the merged table is applied for further operation, an error message occurs, indicating that the current data volume is too large.

Cause:

During table merging, the data volume of the merged table increases significantly. The default maximum data volume of a table is 1000000 rows.

Solution:

Admins can modify the Data Access Volume under System Management > System Setting > General.