Merging data tables with multi-to-multi relationships

1.  Overview

1.1 Problem description

The user has two data tables "FRDemo_PersonnelAlteration" and "FRDemo_PersonnelRoster", and the associated field is "Company name", and the "Company name" of both tables are not unique, as shown below.

At this point, you need to merge two tables into one, but obviously, you cannot create an association relationship and merge them, because there is no data table on the 1st side, so you can use the left/right merge in FineBI.

The data table is shown below after the left and right merge.

1.2 Implementation idea

Implemented using self-service dataset left and right merge.

Note: The operation generates a Cartesian product, and data inflation may create a risk.

2.Operation steps

2.1 Create a self-service dataset

Create a self-service dataset and select all fields under "FRDemo_PersonnelAlteration" as shown below.

2.2 Stitching data using Left and right merge

Add the Left and right merge step, select all fields under "FRDemo_PersonnelRoster", and click "OK", as shown in the following figure.

Since the data contained in the "Company name" field in both tables is the same, you can select the merge method as "left merge" to ensure that no data is lost, and then select "Company name" as the merge basis, as shown below.

Name and save the self-help dataset.

2.3 Effect view

See Section 1.1 of this document for details.