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 effect.

Notes for Direct-Connected Data

• Unlike extracted data, direct-connected data does not allow you to perform the Union All operation on two tables from different data sources.

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

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

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 the tables.

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

2. Click Union All to merge the fields of Contract Table-New 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-New York and click OK, as shown in the following figure.

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, the three fields Region, Contract Region, and Sales Region all represent regional information despite their different names. Manual adjustment is required.

The same principle can be applied to Sum. You can click the dropdown icon, select the matched field, and rename the merge result Sum.

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

Selectable Fields for Merging

When you add tables for union all, the system selects all fields in the tables to be merged by default. You can click the icon next to the table name to select specific fields and specify which ones to include or exclude from the Union All operation, as shown in the following figure.

Renaming Post-Merge Result Fields

Click the post-merge result field and rename it, as shown in the following figure.

Editing Merge Tables

Click Edit Merged Table to return to the table selection page, where you can re-select the tables to be merged, as shown in the following figure.