Multi-table merge/statistical scenario

1.1 Application scenario

There are four tables A, B, C, D, where table A is associated with B, C, D as N:1 respectively. How to merge these tables into one table and count the number of times?

The association between tables is based on "unit", and the 1:N association between table A and other tables is established. The following figure shows.

Processed in a self-service dataset, the effect is shown in the following figure.

1.2 Implementation idea

After uploading the data, multiple tables are merged and statistical scenarios are implemented through a self-service dataset.

1）Add  table A fields via "Select Fields" in the Self-Service Data Set.

3）Merge the B table fields into the dataset by the left and right merge.

3）The duplicate data is merged by group summary to get the correct statistical result.

4）The group summary can be preceded by a function that counts the number of the fields.

5）Repeat the combination of left and right merge and group summary, and continue to merge Table C and Table D to achieve multi-table merge.

2. Upload the data

1）Open "Data Preparation > Add Package", add the package, and rename it as "Multi-table_merge". As shown in the figure below.

2）Open the "Multi-table_merge" package and add the data table below. The following figure shows.

3）Upload the "A_equipment_information" table and fill in the table name automatically, display the data's effect, and click "OK" to confirm the upload. As shown in the figure below.

4）Under the "Multi-table_merge" package, upload several EXCEL tables that need to be merged "shut", "error" and "offline", and follow the same steps as above. The effect after merging is shown in the following figure.

3. Add association

Add associations to the four tables and set the association between "A_equipment_information" and the other three tables 1:N.

1）Add an association for "A_equipment_information" as shown in the following figure.

2）Establish a direct "1:N" association between the "A_equipment_information" table and the "B_shut" table, and click "Confirm and Update" when finished, as shown in the following figure.

3）Continue to add the association between the "A_equipment_information" table and the remaining two tables "1:N". Follow the same steps as above, and when finished, the figure below shows.

4. Add a self-service dataset

In the self-service dataset, add the "A_equipment_information" field first, and then summarize and merge the fields in the three tables.

Under the "Multi-table_merge" package, add the self-service dataset. The following figure shows.

5. Select fields

Name the self-service dataset as "multi-table_merge" and add all fields of the table "A_equipment_information". As shown in the figure below.

Since "A_equipment_information" is a table on the 1 side of the association, you cannot select the fields of the table on the N side, so you need to merge left and right to complete the operation.

After adding the fields in the "A_equipment_information" table to the self-service dataset, merge the fields in the "B_shut" table.

6.1 Left and right merge

1）Use "Left and right merge" to add the fields of "B_shut" table. The following figure shows.

2) Select "data list/Multi-table_merge/B_shut" and add all the fields in the table, as shown in the following figure.

3) Use "intersection" to keep all data. As shown in the figure below.

6.2 Group summary

After merging, there are multiple results of the same unit data at the same time. Therefore, we can add "Group summary" after "Left and right merge" to summarize the data by unit and sort out the corresponding results of the three units, so as to avoid multiple data items.

1) After merging the "shut_time" field, add "Group summary", as follows.

2）Put the merge field "shut_time" of the merge table into the summary column to sum up, and drag the other fields into the grouping column. Then multiple data will be summarized into one, displaying the information of three units. The following figure shows.

6.3 Records count

If you want to count the times of shut of each unit in the "B_shut" table, it is impossible to use the conventional method: repeatedly dragging in the "shut_time" field to count "Records Count". As shown in the figure below.

If there is no null value when adding the merged fields for group aggregation, you can use this method to find the number of times. However, if there is a null value in the merged field "shut_time" in the table, number 1 will be recorded, and the correct statistics of the number of stops will not be obtained.

Therefore, to find "shut times", we need to add a step in the middle of "Left and right merge" and "Group Summary", and judge the "shut_time" field by IF function.

1) In the middle of "Left and right merge" and "Group Summary", "New column" is added. Add a judgment condition to the "shut_time" field. The following figure shows.

2）After adding a new column, enter the formula: IF(shut_time=NULL,0,1). Determine each record, when the shutdown time is empty, display 0, otherwise, 1. Add a new column with the name "Number of shutdown times". As shown in the following figure.

The effect after adding the formula is shown in the following figure.

3）Return to "Group Summary" and add the field "shut_times" to the summary column to sum up the results. The figure below shows.

1) Add "Left and right merge" and use the "Union combined" to merge all the data in "C_error". See section 6.1 of this article for the same steps as above.

2) Perform "Group Summary" on the data, and follow the same steps as above, see section 6.2 of this document. The result is shown in the following figure.

Note: If the amount of data is too large, you can merge "B_shut" and save the dataset. Then create a new self-service dataset to merge the "C_error" table.

To count the error times, refer to Section 6.3 of this document for the procedure.

Using the "D_offline" table, continue to repeat the operation in Section 7, and click "Save" to complete the multi-table merge. The result is shown in the following figure.

9. Effect View

For details, see Section 1.1 of this document.