Column from Other Tables

Version

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.

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.

For example, add fields from table B to table A.

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.