Cluster-Survive Data

Clustering helps identify unique entities after you determine which fields can be used to identify related records.

Using a set of cluster rules, the clustering process partitions a set of records into related sets of entities based on the rules. Each set is assigned a unique cluster ID, which is added as a field to the records. Cluster rules are expressed as sets of fields that have equal values. If the values for two records match for the fields specified in the rule, the records are considered matched and are assigned to the same cluster.

The simplest example of a cluster rule uses a single column. Rows in the table that have the same value for a field in this column are gathered into a set. In the following example, if the cluster rule specifies only the NAME column, then the result yields two sets:

To define identity such that both the NAME and EMAIL1 columns are considered, you can use the AND Boolean operator to construct a NAME and EMAIL1 rule, which yields four sets:

In the previous example, the two Alice records are separated into two sets because the values for EMAIL1 differ. You might want these two records to be identified as one set because alice@alice.net is a common address for both records, but it appears in two different columns. If you want to combine these records together in a set, you must specify an advanced cluster rule using parentheses and the OR Boolean operator to construct the rule NAME and (EMAIL1 or EMAIL2):

When you use the OR operator this way, matching values in the NAME column and matching values in either the EMAIL1 or EMAIL2 columns cause the records to be grouped into one set.

It is possible to use the OR operator without parentheses. For example, the rule EMAIL1 or EMAIL2 still returns a single set for the Alice records. However, when the rule uses AND as well as OR operators, an order of precedence applies. Without specifying the parentheses, the previous rule would be evaluated as (NAME and EMAIL1) or EMAIL2, which yields very different results.

Multiple cluster rules can be specified, and are all used when grouping records together.

The clustering process identifies a set of records that are logically related, after which you can use survivorship rules to create a survivor record. The survivor record is considered the best single representative of the clustered data. Survivorship processing uses rules for base record selection and column selection. For example, after unique records are identified and clustered, you might find multiple addresses listed for a particular individual. The rules have identified these records as similar, but you must determine which address should be used as the standard.

Survivor base record rules are used to select a single record as the template for the surviving record. The rules apply only to records that share a common identity as determined by the cluster rules. The template can be further modified by applying survivor column rules that update individual field values from the records in the set.

The base record is determined by applying operators to column values. For example, if your record contains a date field indicating when it was last updated, you can use a rule such as Max(UPDATED) to select the most recent update to use as your base record:

Alternatively, you could use the most frequent value for city. For example, you could use Highest Occurrence (CITY) in your rule, which would yield two possibilities for the base record:

You can have only one base record, so you must either refine the first rule or provide another rule that selects a unique record.

If you want to refine the results, you can specify another rule that is linked to the first rule by using the Apply to previous rule output setting. When this option has been selected, only the records that match the first rule are provided to the second. For example, if the first rule is Highest Occurrence(CITY) and the second rule is Max(UPDATED), the base record represents the last known address for Alice in Tucson:

Sometimes, chaining rules can result in no records being selected, even though at some point in the process a unique record was selected. For example, if you added another chained rule that required the UPDATED field to be greater than January 1st, 2013, no records would be selected. Even though a unique record was selected by the second rule in the chain, it does not survive the third rule. Use the Stop processing rules after the first rule yields a single record option to stop processing when a unique record has been identified.

Alternatively, you can create another rule, or chain of rules, that uses a different strategy to select a base record. If the Apply to previous rule output option is not selected for a rule, then the complete group of matched records is provided for the rule to process.

Survivor column rules apply field-level edits to the survivor base record, where field values are selected from the clustered group of records. This is useful when integrating data from different sources where the fields might be absent or less reliable.

For example, given the following data, and assuming that the first record has been selected as the base record, you might want to incorporate the email address from the second record into the base record:

To do this, you would build one or more rules that identify the desired field values and copy them into the base record. In this example, a rule such as Not Null(EMAIL) selects the second record. Specifying the EMAIL field as the field selection then causes the (null) value to be updated:

The fields used in the rule are not required to be related to the fields specified in the field selection list. For example, you could use the Updated field to identify the record, and then copy the Street and City field values to the survivor without including Updated in the field selection list.