SSIS:MatchUp:Survivorship Pass-Through

← SSIS:Data Quality Components

Select ‘Use Simple Pass-Through’ if you would like the contents of the checked field names to be passed to the Source output stream without modification, in addition to the Output Columns specified in Options.

Select ‘Use Advanced Survivorship’ if you would like to gather data from other matching records to pass into a single output stream record.

Source Pass-Through Columns

This tab allows you to add specific fields from the input Source file to the output file.

Pass Through

Check this box if you would like the contents of the checked field name to be passed to the Source output stream without modification, in addition to the Output Columns specified in Options.

Column Name

The column names from the original input table.

Current Usage

Where the field is currently being used as input.

Data Type

The data type of each column.

Length

Maximum number of bytes returned.

Precision

Maximum number of digits.

Scale

Maximum number of digits to the right of the decimal point.

Advanced Survivorship

Survivorship can merge data from matching record(s) into a single output record. This allows you to establish criteria for each data column individually instead of using the default method of writing full record content to output.

For example, consider the following matching records:

Name	Address	Last Update	Amount
John Doe	123 Main St	10/25/2003	420
John Doe	123 Main St	4/16/2012	100
Mr John Doe	123 Main St	8/6/2008	56

Using the Golden Record selection by latest update, the selected remaining record would be:

Name	Address	Last Update	Amount
John Doe	123 Main St	4/16/2012	100

But if we set survivorship for longest name and greatest amount, we can populate our remaining record with values from other matching records.

Name	Address	Last Update	Amount
Mr John Doe	123 Main St	4/16/2012	420

Output Column Name

The output column name. You choose this name when you add a column through the "Add Column" button. Double-click an existing column name to change its name.

Consolidation Method

This method determines how matching data is merged. Specify the consolidation method to be used on the selected output column. The following examples that have Prioritization use the Lowest Salary Prioritization.

First Data

First Non-Empty

Join

Join With Spaces

Add

Average

Most Frequent

Stack Group

Source Column or Expression

This is where you specify the source of the data which will populate the output column. As with Simple Pass-through, the most common usage is to use the contents of a Source Column, although you can also use a custom expression which may concatenate two fields for example.

Ex. An custom expression like column ZIP + “ “ + CUST_ID could be used to populate a new column with a proprietary account number.

To bring up the Survivorship Expression editor, double-click the respective cell.

Prioritization

When MatchUp identifies a group of duplicates, configuring Prioritization allows you to order the column values, enabling you to select the lowest or highest value (left to right for strings) to output. For more information, see Prioritization.

Click the "..." button under the prioritization column to open the prioritization window for the selected row.

Prioritization

When MatchUp identifies a group of duplicates, configuring Prioritization allows you to order the column values, enabling you to select the lowest or highest value (left to right for strings) to output.

Source

Specify the source of the data which will populate the output column. As with Simple Pass-through, the most common usage is to use the contents of a Source Column, although you can also use a custom expression which may concatenate two fields for example.

For Example: A custom expression like column ZIP + “ “ + CUST_ID could be used to populate a new column with a proprietary account number.

Default

By default, the output column will be populated with contents from source column contents. This option will be greyed out if the output column is a newly created column, which does not exist in the source table.

Use a Custom Expression

You can have the output column populated with data other than the contents of the source column by creating a custom expression. A valid expression may be the contents of a different field, data concatenated from multiple fields, or custom values created with any valid expression. Select the button to the right to access the expression builder to help you select any of the available valid options. For more information, see Expression Builder.

Prioritization

To bring up the Survivorship Expression editor, double-click the respective cell or select the Expression Builder button to the right.

No Prioritization

The Golden record, the record selected for output by the criteria you specified on the Options tab, will contribute the data for this column to be written to the output record.

Use a Custom Expression

This allows you to create a custom expression which will be evaluated for each record in the matching group. If this option is chosen, you also specify Lowest or Highest value of that expression to order the return values of those matching records. The record whose value is at the top of that order will have its data contribute to the output record. With use of different custom expressions, it is possible to have output records with data from many different matching records. For more information, see Expression Builder below.

Expression Builder

The Expression Builder allows for creating custom expressions for Survivorship. The expression editor includes Industry Standard Functions and Operators for building custom rules. Also included are Melissa Data proprietary functions for prioritization according to the quality of data. The Expression Builder also allows for selecting which set of data will be outputted as the surviving data.

Expression

Specify the expression to be used.

Use a pre-built expression

Select a pre-built expression from the drop down menu.

Use the specified expression

You can choose to make your own custom expression. Use the Expression Elements (see below) section for lists of the various elements you can use in your custom expression.

Expression Elements

Contains categorized lists of the possible elements you can use in a custom expression. Double-click an element to have it inserted into the "Use the specified expression:" text field.

See Custom Expression for a detailed explanation of the Expression Builder.