Adding Columns to the Current Table

Version

Application Scenarios

FineBI has added the function Columns in other tables, which is similar to the funcitions LOOKUP and SUMIF in Excel. By using this function, you no longer need to configure an intermediate table.

If table A and table B have a field that be matchd, you can add a column in table A:

• Directly add the fields of table B into table A.

• Or summarize the fields of table B according to Match basis and then add them to table A.

Functions

Columns in other tables has similar effects as Left and Right Merge in some circumstances, such as merging the fields from other tables to the current table.

However, the advantage of Columns in other tables is to conduct the aggregate calculation based on the Match basis, and then add the calculation results to the current table. This function cannot be achieved with Left and Right Merge and can provide richer merging results.

Create an Analysis Subject and add sample data: Store Info, Product Info, and Product Sales.

Adding Product Name

The table Product Sales only includes Product Code, without Product Name.

1. Adding Product Name to the Current Table

Click Add field > Select the field Product Name from the table Product Info.

2. Adding Match Basis

What is the basis of the merging between the added field and the current table? So a Match basis is needed here. The current table and the table Product Info both shares the field Product Code, which is also distinguishable. You can add this field as a Match basis.

3. Selecting the Group Way as the Maximum

Since one product code corresponds to only one product name, choosing either maximum or minimum as the Group way will both lead to the Product Name.

Summing up the Sales of Each Store

The table Store Info includes the basic information of each store. How to use the function Columns in other tables to sum up the sales of each store?

1. Adding Field

Click Columns in other tables > Add the field Sales from the table Product Sales.

2. Adding Match Basis

The current table and the table Product Sales both shares the field Store Code, which can be taken as a Match basis.

3. Setting Group Way

Group by Store Code > Sum up the sales of grouped stores. Then add the grouped values to the current table.

Select Sum as the Group way: summing up the total sales of each store.

4. Adding Group Condition

You can choose to summarize the data that meet certain conditions.

Filter out the data after 2019/06/01, to sum up the sales after June 1, 2019.

Click OK to get the total sales of each store after June 1, 2019.

You can also set Columns in other tables for the table Store Info.Add fields from the table Product Sales and calculate Order Quantity, Cost Amount, and Sales of each store. Take the field Store Code as a Match basis. The procedures are shown like this:

The effect is shown below: