Merge Operator

Estimated Reading Time:  9 minutes

Overview

The Data Workflow Merge operator lets you combine or separate data from 2 Data Tables using key/value pairs. Controlling how data merges depends on the chosen feature and how you use the operator's 2 input ports.

Let's look at the operator's features:

• Intersection: Combines all table values that share the same key.

• Inner: Combines all table values that share the same key. Unlike the Intersection feature, the Inner feature also lets you add one table's values to the other table.

• Outer: Separates out values from one table that don't share the same key as the other table.

How the information merges depends on the feature used and the operator's inputs. If you want to combine all the information the 2 Data Tables share, use the Intersection or Inner features. If you prefer to keep the information that doesn't match between the 2 Data Tables, use the Outer feature.

You'll find the Merge operator under the Table group to the left of your canvas.

What You'll Learn

In this article, you'll learn:

• When to use the Merge operator.

• About the Merge operator's settings.

• How to add and configure a Merge operator.

• How to modify a Merge operator.

• How to copy a Merge operator.

• How to remove a Merge operator.

About the Info Window

Here's what the Merge operator looks like, along with its Info window:

And here's a breakdown of each setting in the Info window:

Setting	Description
Category	Grayed-out and non-adjustable setting indicating the operation type.
Label	Sets the label for your operator. The label you enter here shows beneath your operator on your Data Workflow canvas. This field is optional, but set a label if you use more than one of the same operator type. A label helps you identify your operators without opening any Info windows.
Merge Type	This drop-down lets you select how you want your data sets to merge.
Inner	Merges rows from the lower input table into the upper input table. The values merged must share the same key.
Outer	Separates all values from the upper input table whose key doesn't match the lower input table.
Intersection	Combines all values whose keys match. NOTE  You can use this option like a filter. You can filter a key to help you find matching values from both tables.
Keep Flag	This indicator displays in the DevTools Console for each row merged. Selecting True creates a flag that describes how you chose to merge the information. If True, a _merge_flag displays in the merged row. NOTE  Left refers to the Data Table connected to the upper input port. Right refers to the Data Table connected to the lower input port.
Both	Displays when merged values have the same keys in both tables.
Left Only	Flags rows from the upper input with a key that doesn't match the other table.
Right Only	Flags rows from the lower input with a key that doesn't match the other table.
Renaming	This drop-down lets you rename, replace, or combine merged data.
Rename	Adds a suffix of _r to the merged data. If you perform an Inner merge, the upper input's values remain the same, but the lower input's values get the _r suffix.
Replace	Overwrites one of the table’s values with the other table's values. If you perform an Inner merge, the lower input's values replace the upper input's values.
Push	Takes the values of both tables and combines them into an array. If you perform an Inner merge, the lower input's values combine with the upper input's values in an array. The resulting push is nameUpperValue,nameLowerValue.
Rows to Keep	This drop-down lets you pick which values you want to keep from your Data Tables. This setting is important when the values you want have more than 1 match.
First	Keeps the first row that matches in both tables.
Last	Keeps the last row that matches in both tables.
All	Keeps every row of the 2 tables and combines them into 1 array.
Merge Key	This is the reference key of the data you want to merge. This key can be the heading of a column in your Data Tables.

Adding a Merge Operator

In this article, you'll explore the Intersection feature. Let's say you want to combine data from 2 Data Tables. The first table has names of insurance policyholders and their birthdates. The second table has names of insurance policyholders and their insurance premiums. There are similar names in each table and some that are different. Now you want to merge the birthdates and premiums based on the name of the policyholder. That means the policyholder's name is your key. If the same name exists in both tables, their corresponding birthdates and premiums merge into a new table.

Here's how the completed use case looks in the Module Builder:

Here's how the completed use case looks in Express View, including a peek at the DevTools Console:

What You Need

To set up this use case, you need:

• 2 Data Table components

• 1 Button component

• 1 Data Workflow component

To set up your Data Workflow, you need:

• 2 Input operators

• 1 Merge operator

• 1 Console operator

NOTE  These instructions assume you have a module open, saved, and with a title.

Configure the First Data Table Component

First, set up a Data Table with a list of annuitants and their dates of birth.

1.

In the Module Builder, drag and drop a Data Table component onto your canvas.

2.

Enter dtAnnuitant in the Label and Property Name fields.

3.

In the data table, enter the following:

4.

Click Save.

Configure the Second Data Table Component

Next, set up a Data Table with another list of annuitants and their insurance policy premiums.

1.

Drag and drop a Data Table component onto your canvas. Place it under your other Data Table.

2.

Enter dtPremiums in the Label and Property Name fields.

3.

In the data table, enter the following:

4.

Click Save.

Configure the Data Workflow Component

Next, set up the Data Workflow and configure the Merge operator. Because you are merging all values with a matching key, it doesn't matter how you input the Data Tables. The result is the same regardless of which table is the upper and lower input. For this example, connect the first table to the upper input port and the second table to the lower input port. Add a Console operator so you can see how the data merges in the DevTools Console.

1.

Drag and drop a Data Workflow component onto your canvas. Place your Data Workflow below your Data Table components.

2.

Enter dwfMergeIntersection in the Canvas Label Text and Property Name fields.

Configure the First Input Operator

1.

Drag and drop an Input operator onto your Data Workflow canvas.

2.

Configure the Input operator's Info window as follows:

Configure the Second Input Operator

1.

Drag and drop another Input operator onto your Data Workflow canvas.

2.

Configure the Input operator's Info window as follows:

Configure the Merge Operator

1.

Drag and drop a Merge operator onto your Data Workflow canvas.

2.

Configure the Merge operator's Info window as follows:

3.

Connect the output port (right) of the dtAnnuitant Input operator to the upper input port (left) of the Merge operator.

4.

Connect the output port (right) of the dtPremiums Input operator to the lower input port (left) of the Merge operator.

Configure the Console Operator

Add a Console operator as an output to the Merge operator. This lets you see the result of the merge.

1.

Drag and drop a Console operator onto your Data Workflow canvas.

2.

Configure the Console operator's Info window as follows:

3.

Connect the output port (right) of the Merge operator to the input port (left) of the Console operator.

4.

Click Save.

Configure the Button Component

Finally, to trigger the Data Workflow, add a Button component.

1.

Drag and drop a Button component onto your canvas. Place your Button component above your Data Workflow component.

2.

Enter btnMergeIntersection in the Property ID field.

3.

Enter Intersection Merge in the Label Text field.

4.

Select Event as the Action Type.

5.

Enter dwfMergeIntersection in the Trigger on Click field.

6.

Click Save.

7.

Save your module.

Now you can test out your Data Workflow. When you click the button in Express View, the Data Workflow triggers. Open the DevTools Console and you’ll see the results of the intersection function. The Data Workflow combines Kris Marrier and Mattie Poquette with their birthdates and insurance premiums, as expected. Ezekiel Chui and Willow Kusko don’t share information between tables, so they don’t merge or display in the new table.

Changing a Merge Operator’s Settings

You can revisit and make changes to this operator.

1.

Click the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2.

Click the (Settings) button to open the Data Workflow canvas.

3.

Click the operator to open its Info Window.

4.

Make changes to the operator's settings as needed.

5.

Click Save.

6.

Save your module.

Copying a Merge Operator

You can make a copy of your operator using familiar keyboard settings. You can copy and paste an exact copy of your operator, matching all settings.

1.

Hover over the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2.

Click the (Settings) button to open the Data Workflow canvas.

3.

Click the operator you want to duplicate.

4.

On your keyboard, press Command + C (Mac OS) or Ctrl + C (Windows/Linux) to copy the operator.

5.

On your keyboard, press Command + V (Mac OS) or Ctrl + V (Windows/Linux) to paste the copied operator(s) to the Data Workflow canvas.

6.

Click Save.

7.

Save your module.

TIP  Did you know you can copy an operator and paste it into a different Data Workflow? You'll use the same steps outlined above.

Removing a Merge Operator

Lastly, you can delete this operator from your Data Workflow canvas. You can also use these same steps to delete a connection between two operators.

1.

Hover over the Data Workflow component.

A 5-button toolbar displays above the component on hover-over.

2.

Click the (Settings) button to open the Data Workflow canvas.

3.

Click the operator you want to delete.

4.

On your keyboard, press Delete.

5.

Click Save.

6.

Save your module.