Data Matching/Query

Application Scenario

During data processing, when data is scattered across multiple data tables, you may need to match and retrieve data among data tables based on a specific field (primary key) or multiple fields (composite key).

Two data tables are presented in this example, Internet_User Information Table and Internet Access Statistics.

Internet_User Information Table records information about the user ID but does not store information such as the access date, access time, and dwell time. You can use the Join function to associate and query data and merge the required data into a data table, facilitating the subsequent analysis of the user access data.

Note:

User IDs are unique.

Function Description

In FineBI, you can realize the effect by Join (similar to the VLOOKUP function in Excel).

Download the sample data Sample Data.zip.

Analysis Subject Creation

Create an analysis subject and upload the downloaded sample data, as shown in the following figure.

Field Matching

1. Select Internet_User Information Table, click Field Settings, and select the User ID, Username, Age, Gender, and User Type fields, as shown in the following figure.

2. Click Join, select the Internet Access Statistics table, select the User ID, Access Platform, Statistical Date, Pageview, Access Time, Bounce Time, and Total Dowell Time fields in the table, and click OK, as shown in the following figure.

Set Mode to Left Join and set the merge basis to User ID. With the setting, information such as the access date, access time, and dwell time is matched based on User ID in Internet_User Information Table and merged into a table, as shown in the following figure.

Note:

For details about Join, see Join.

Click Save and Update to save and update the data.

Effect Display

You can obtain the result table where information about different users (such as the access date, access time, and dwell time) is matched based on User ID in the Internet_User Information Table table, as shown in the following figure.