Overview
Version
FineBI Version | Functional Change |
6.0 | - |
6.0.5 | Added the Create Merge Calculation function. |
6.0.11 | Added the source table of the added fields in Details. |
Application Scenarios
The Column from Other Tables function is added in FineBI, which is similar to the function LOOKUP and SUMIF in Excel. By using this function, you no longer need to configure an intermediate table.
For example, if table A and table B have a field that can be merged, you can add a column in table A using either of the following method:
Directly add the fields from table B to table A.
Summarize the fields of table B according to the match basis and then add them to table A.
Introduction
The Column from Other Tables function has a similar effect to the Join function in some cases, for example, merging fields from other tables into the current table.
However, the Column from Other Tables function has a unique advantage, that is, allowing aggregation calculations on fields based on the match basis before merging them. This cannot be achieved by the Join function, enabling you to obtain richer merging results.
Example
Create an analysis subject and add sample data from Store Info, Product Info, and Product Sales tables.
Adding the Product Name
The Product Sales table contains only the Product Code column, without the Product Name column. As shown in the following figure, Product Code is inconvenient to view.
So you need to add the Product Name to the current table. Click Add Field, and select Product Name from the Product Info table, as shown in the following figure.
To match the added field with the current table, you need to add a match basis.
Both the current table and the Product Info table have the same distinguishable field, Product Code. So you can add this field as a match basis.
Select Maximum as the summary method. Since one product code corresponds to only one product name, Product Name is always displayed when you choose either maximum or minimum as the summary method, as shown in the following figure.
Summing up the Sales of Each Store
The Store Info table contains the basic information of each store. How to use the Column from Other Tables function to summarize the sales of each store?
Click Column from Other Tables and add the Sales field from the Product Sales table, as shown in the following figure.
Use the Store Code field existing in both the current table and Product Sales table as a match basis, as shown in the following figure.
Set the summary method.
Group by Store Code and summarize the sales after grouping. Then add the grouped values to the current table.
Select Sum as the summary method to sum up the total sales of each store.
Add summary criteria.
You can select data that meet certain criteria to summarize.
Filter out data generated after 2019/06/01 to summarize sales, as shown in the following figure.
Click OK to obtain the total sales of each store after June 1, 2019, as shown in the following figure.
Functions
For example, add fields from table B to table A.
Function | Description | |
Selecting fields | Select a table B from the current analysis subject and add fields to the table A. You can add multiple fields. If you select a table from other analysis subjects, all configurations of the field will be reset. | |
Setting fields | Click | Group Way Different field types have different group ways. For details, see Group Summary. The functions maximum and minimum have been added in Column from other tables. |
Group Condition Values meeting the filter condition will be grouped and calculated. Empty value will be displayed if values do not meet the condition. You can add conditions and formulas when setting group conditions, and all fields in table B can be selected in these two ways.
| ||
Match basis | There are associated fields in two tables to be matchd. And these two tables will be matchd to the left. If there are associated fields between fields added and the current table, the Match basis will be automatically added. You can also manually add a Match basis. | |
Another Example
You can also use the Column from Other Tables function for the Store Info table . Add fields from the Product Sales table and calculate order quantity, cost qmount, and sales of each store. For example, use the Store Code field as match basis. The procedure is shown in the following figure.
The effect is shown in the following figure.
Previous：Field Settings
