Merge Operator: Outer

Overview

The Merge operator offers various options for merging data. The operator also lets you control how data merges with its 2 input ports. This use case explores merging data with the operator's Outer feature. This is a unique feature that separates out values that don't share the same key between the 2 Data Tables. So, all values from the upper input table whose key doesn't match the values of the lower input table display in the new table.

For this example, let's say you have a Data Table that has insurance policyholders' names and their birthdates. Then, you have a second Data Table holding policyholders' names and their premiums. There are similar names in each table and some that are different. Instead of merging values with matching keys, you only want values that don't match that key. For this use case, set up the operator's key as the policyholder name. After the merge, only upper input values that don't have the same policyholders as the lower input display in the new table.

Here's how your module 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'll Learn

In this article, you'll learn how to use the Merge operator's Outer feature.

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

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

Configuration

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:

annuitant

dob

Kris Marrier

01/01/1990

Mattie Poquette

12/12/1989

Ezekiel Chui

04/05/1980

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 your Data Table under your other Data Table.
2. Enter dtPremiums in the Label and Property Name fields.
3. In the data table, enter the following:

annuitant

premium

Kris Marrier

50000

Mattie Poquette

25000

Willow Kusko

20000

4. Click Save.

Configure the Data Workflow Component

Next, set up the Data Workflow and configure the Merge operator. How you configure the operator's inputs becomes important when using the Outer feature. Every upper input value whose key doesn't match the lower input values displays in your new table. For this use case, input the dtAnnuitant Data Table into the upper input port. Then, input the dtPremiums Data Table into the lower input port. Finally, 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 dwfMergeOuterLeft 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:
Setting Value

Category

Input

Component

dtAnnuitant

Required

Yes

Source

Default

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:
Setting Value

Category

Input

Component

dtPremiums

Required

Yes

Source

Default

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:
Setting Value

Category

Merge

Label

Merge Outer

Merge Type

Outer

Keep Flag

False

Renaming

Replace

Rows to Keep

First

Merge Key

annuitant

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

1. Drag and drop a Console operator onto your Data Workflow canvas.
2. Configure the Console operator's Info window as follows:
Setting Value

Category

Console

Label

Outer Merge Result

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 below your Data Workflow component.
2. Enter btnMergeOuter in the Property ID field.
3. Enter Outer Merge in the Label Text field.
4. Select Event as the Action Type.
5. Enter dwfMergeOuter in the Trigger on Click field.

6. Click Save.
7. Save your module.

Now, preview your module in Express View and open the DevTools Console. Click the button to trigger the Data Workflow. All you see is Ezekiel Chui's name and birthdate. Because Kris Marrier and Mattie Poquette exist in both tables, the Outer feature excludes them from the new table. This feature keeps only the upper input values that don't match those of the lower input.

For fun, switch the inputs of your Merge operator and trigger the operation. You'll see very different values in your new table.

Lab

You can view this complete use case here: https://training.unqork.io/#/form/601aec17f9d1c6025d9bda8e/edit.