Union All

Version

Application Scenario

If you perform Union All (same as that in SQL statements) on data tables, a union result set is generated.

A company's order information may be stored separately in multiple branches for historical reasons, resulting in unsmooth information exchange. In this case, you can use the Union All function in FineBI to merge multiple data tables into one table, coordinating all order information.

The Union All function allows you to manually match fields with the same meaning for merging, requiring no identical matching fields.

The following figure shows the merging effect.

Notes for Direct Connect Versions

• To perform Union All on direct connect data, the two data tables storing the data cannot come from two different data sources, but can in the extraction versions.

  After merging, an error message "[DIRECT-ETL] unsupported data source: databases on different hosts/ports" pops up.

• For real-time data, only tables in some databases can be merged with Excel tables. For details, see Databases Supporting Integrated Analysis with Excel.

To download the sample data, click Contract Table Sample.zip.

A company stores its data of New York, Chicago, and Los Angeles in three separate tables, causing inconvenience for analysis. You can use the Union All function to merge them together.

1. Create an analysis subject and upload the downloaded sample data.

2. Click Union All to merge the fields of Contract Table-Yew York, Contract Table-Chicago, and Contract Table-Los Angeles.

3. Select Contract Table-Chicago and Contract Table-Los Angeles as the tables to be merged with Contract Table-Yew York and click OK.

4. Go to the Union All configuration page. The system will automatically match fields with consistent names and consider fields with inconsistent names as different ones.

5. In this example, manually match Region, Contract Region, and Sales Region (different name but same meaning).

The same principle can be deducted for Sum. You can click the Dropdown icon, select the matched field, and rename the merge result as Sum.

Then the data of New York, Chicago, and Los Angeles is merged into one table, facilitating later analysis.

Selecting Partial Fields of the Tables to be Merged

All fields of the tables to be merged are added as the merging basis by default, but you can click the ... icon and click Select Field to select partial fields.

Renaming Post-Merge Result Fields

Click the post-merge result field and rename it.

Editing Merge Tables

Click Edit Merge Table to return to the table selection page, where you can reselect the tables to be merged.