Overview
Model splicing is an important capability of business dataset processing, aiming to provide users with easy-to-use, low-threshold, and efficient data processing before conducting data analysis and visualization. By transforming, merging, and other data processing operations, it provides end business users with a more user-friendly business model.
Introduction to Model Splicing
Model splicing (JOIN) is used to connect different business data models. When analyzing data, if the data to be analyzed is stored in different models, you can use model splicing to connect multiple tables and form a new model for data analysis. This helps to better understand relationships between data and gain a better understanding of the data.
Application Scenarios
There are three types of application scenarios for model splicing: left join, inner join, and full join.
| Splicing Methods | Splicing Descriptions |
|---|---|
| Left Join | All data from the left source table is displayed, only the data that meets the association criteria from the right source table is displayed, and empty values are filled in the areas where the data is insufficient from the right source table. |
| Inner Join | Rows that have values of common dimensions and match in both the left and right source tables are included in the results; corresponds to the inner join in SQL statements. |
| Full Join | All data from the selected source tables is displayed, and empty values are filled in for each table where the association criteria are not met; corresponds to the full join in SQL statements. |
Operating Steps
Select the business model and drag it into the splicing canvas, the canvas background area will have a highlighted prompt, place it according to the prompt.
When you add the second table, the merge settings drawer will automatically pop up. Follow the steps in the diagram to complete the merge settings.
After configuring, you can click on the link icon to view the relationship.
If you don't need a certain node, you can hover over the table and click on the delete button.
Custom join conditions
When you need to use custom calculations as join fields, follow these steps:
- Select "Create calculated field" from the drop-down menu.
- Enter the expression to create the calculation.
Filter before join
Before joining, if you need to do some data processing such as filtering out certain rows, you can follow the steps below.
Multiple Common Dimension Associations
The significance of multiple merging criteria is to ensure the uniqueness of data when merging. For example, the concatenation of the following two tables requires two common dimensions: city and year.
| City | Year | Income |
|---|---|---|
| Beijing | 2021 | 1300 |
| Beijing | 2022 | 1590 |
| City | Year | Expense |
|---|---|---|
| Beijing | 2021 | 1525 |
| Beijing | 2022 | 1630 |
Data Preview
Users can click on Data Preview to observe if the concatenated results meet expectations.
Batch Editing of Concatenation Results
Users can edit the display names of concatenated result fields and delete unnecessary ones, as shown in the figure below.
Frequently Asked Questions
1. Will null values match each other?
If there are null values in the join criteria of the two tables being merged, the null values will not match each other.
2. What does a calculated column from the source table mean in the result table?
If the concatenated result includes a calculated column from the source table, it is equivalent to helping users create a calculated field on the result table based on the calculation logic from the underlying table, and the calculation is performed after the concatenation.
3. Can data inflation occur during concatenation?
During the concatenation calculation, if a Cartesian product occurs, it will result in data inflation. In this case, the system will prompt the user with the dimension values where the inflation occurs to help resolve the inflation problem.
4. Is the concatenation result a physical table?
The concatenation result is a logical table, and each query will perform Join calculations according to the concatenation definition.