Consolidation Mapping Example
Your organization needs to consolidate duplicate customer records. To consolidate the customer records, you group the data with a Key Generator transformation and use the Consolidation transformation to consolidate the records.
You create a mapping with a data source containing customer records, a Key Generator transformation, a Consolidation transformation, and a data target. The mapping groups customer records, consolidates the groups, and writes a single consolidated record.
The following figure shows the mapping:
Input Data
The input data that you want to analyze contains customer information.
The following table contains the input data for this example:
Name | Address | City | State | Zip | SSN |
|---|
Dennis Jones | 100 All Saints Ave | New York | NY | 10547 | 987-65-4320 |
Dennis Jones | 1000 Alberta Rd | New York | NY | 10547 | 987-65-4320 |
D Jones | 100 All Saints Ave | New York | NY | 10547-1521 | |
Key Generator Transformation
Use the Key Generator transformation to group the input data based on the ZIP code port.
The transformation returns the following data:
SequenceId | GroupKey | Name | Address | City | State | ZIP Code | SSN |
|---|
1 | 10547 | Dennis Jones | 100 All Saints Ave | New York | NY | 10547 | 987-65-4320 |
2 | 10547 | Dennis Jones | 1000 Alberta Rd | New York | NY | 10547 | |
3 | 10547 | D Jones | 100 All Saints Ave | New York | NY | 10547-1521 | 987-65-4320 |
Consolidation Transformation
Use the Consolidation transformation to generate the consolidated record.
Configure the Consolidation transformation to use the row-based strategy type. Select the Modal Exact strategy to choose the row with the highest count of the most frequent values. The Modal Exact strategy uses the values from that row to generate a consolidated record. The consolidated record is the record with the "Y" value in the IsSurvivor port.
The transformation returns the following data:
GroupKey | Name | Address | City | State | ZIP Code | SSN | IsSurvivor |
|---|
10547 | Dennis Jones | 100 All Saints Ave | New York | NY | 10547 | 987-65-4320 | N |
10547 | Dennis Jones | 1000 Alberta Rd | New York | NY | 10547 | | N |
10547 | D Jones | 100 All Saints Ave | New York | NY | 10547-1521 | 987-65-4320 | N |
10547 | D Jones | 100 All Saints Ave | New York | NY | 10547-1521 | 987-65-4320 | Y |
Consolidation Mapping Output
Configure the Consolidation transformation so that the mapping output contains only consolidated records.
In this example, you are reasonably confident that the most frequent values selected by the Modal Exact strategy are the correct port values. To write only consolidated records to the mapping target, you select the Advanced view and set the output mode to "Survivor Only."
When you run the mapping, the mapping output only contains consolidated records.
