Handling Match Failure in Join

Background

When you perform Join, if the merge basis fields of the two tables are different, the match will fail. The values of the fields that can be successfully merged must be the same.

However, in practical use, you may often encounter situations where there are slight differences in the fields of the two tables, which requires us to handle the field values to merge the tables successfully.

Implementation Method

Use the Create Merge Calculation function to process the merge bases.

This function is only supported in V6.0.5 and later versions.

You may wonder how to merge Sales Volume and Store Number of the cities into one table.

The merge basis field City in the right table has an extra word City compared to the right table, which causes the matching to fail. In this case, you can solve the problem with the following method.

Download the example data Example.rar and upload it to the analysis subject.

Join

1. Go to the editing page of Store Number and click Join. Perform the left and right merge with the Sales Volume table, as shown in the following figure.

2. Since the City field in the Sales Volume table can not be matched with the City field in the Store Number table, you need to create an auxiliary merge basis field.

Click the City field of the Sales Volume and select Create Merge Calculation from the drop-down list, as shown in the following figure.

3. Enter the formula City+" City", as shown in the following figure.

4. The City fields in these two tables can be matched after the process.

Two tables utilize the Region ID field as the merge basis to perform the left and right merge. However, Region ID in one table is a text field, while it in another is a value one.

In this case, you can also use Create Merge Calculation.

1. Open the Example_1 Table and select Create Merge Calculation, as shown in the following figure.

2. Convert Region ID to a text field.

3. The two tables can be successfully merged after the process.