AO Compile Master
Overview
AO Compile Master accepts a single input stream. It is designed to create a "Best Record" in the master (first-ordered) record of a match group. For up to ten fields, it will use the defined logic to build this consolidated master record. You can connect multiple AO Compile Master macros in parallel or sequence to operate on an unlimited number of fields. It is a common practice to use a Calculate tool to copy the original data into new fields and then use AO Compile Master on those fields. This allows you to see both the original data and the compiled data simultaneously to ensure that results are as expected.
AO Compile Master configuration parameters
AO Compile Master has two sets of configuration parameters in addition to the standard execution options: Source, and Target.
AO Compile Master Source tab
Parameter | Definition |
|---|---|
Match ID | Required. Match ID linking records. This is usually generated out of the matching process or out of AO Match Append Info. Default: Blank. |
Group order | Required. Used for ordering of records for proper data consolidation and master identification. The master is assumed to be the first record within each ordered match group. We recommend using the group order output generated by AO Match Append Info. If missing, the order will be assumed to be the physical record order within the match group. Default: Blank. |
Multi-field link Multi-field operation Multi-field priority | Optional. These parameters are used to copy multiple fields from a single record to the master, using a "nominator" field to select the record to be copied. This works in two steps. First, one record is selected from the set of records in each group using the Multi-field link and Multi-field operation settings. Second, Target tab Field operations configured as "Multi-field" copy fields from the selected record into the master record. The specified multi-field link field is evaluated using the Multi-field operation (Min, Max, or First Valid) to select the record from each group. You can optionally specify a Multi-field priority field to help select the record when there are ties. If no priority is specified, Group order value defines priority. Priority is most critical when the operation is First Valid. |
Consider the following example:
The linkage field specification shown in the first screenshot above finds the record in each Match ID group containing the most recent Date, using GrpOrd to break ties, and then copies the Addr, Addr2, City, State, and Zip fields from that record to the master record. In the sample input data below, we've highlighted the master records in blue and the selected linkage record in red (in group 3 they are the same record).
MatchID | GrpOrd | Address | Addr2 | City | State | ZIP | Date |
|---|---|---|---|---|---|---|---|
1 | 1 | 123 main st | apt 15 | anytown | co | 80808 | 01 Jan 2002 |
1 | 2 | 123 main st | apt 12 | anytown | co | 80808 | 10 Mar 2004 |
1 | 3 | 123 main st | apt 15 | anytown | co | 80808 | |
2 | 1 | 457 oak st |
| boulder | co | 83333 | 13 Jul 1999 |
2 | 2 | 457 oak st | apt 12 | boulder | co | 83333 | 20 Aug 2000 |
3 | 1 | 1515 Walnut | #1 | boulder | co | 83333 | 15 Apr 2004 |
3 | 2 | 1515 Walnut |
| boulder | co | 83333 | 10 Apr 2004 |
3 | 3 | 1515 Walnut st | #3 | boulder | co | 83333 | 01 Apr 2004 |
The resulting data after consolidation has changed the master records:
MatchID | GrpOrd | Address | Addr2 | City | State | ZIP | Date |
|---|---|---|---|---|---|---|---|
1 | 1 | 123 main st | apt 12 | anytown | co | 80808 | 01 Jan 2002 |
1 | 2 | 123 main st | apt 12 | anytown | co | 80808 | 10 Mar 2004 |
1 | 3 | 123 main st | apt 15 | anytown | co | 80808 | |
2 | 1 | 457 oak st | apt 12 | boulder | co | 83333 | 13 Jul 1999 |
2 | 2 | 457 oak st | apt 12 | boulder | co | 83333 | 20 Aug 2000 |
3 | 1 | 1515 Walnut | #1 | boulder | co | 83333 | 15 Apr 2004 |
3 | 2 | 1515 Walnut |
| boulder | co | 83333 | 10 Apr 2004 |
3 | 3 | 1515 Walnut st | #3 | boulder | co | 83333 | 01 Apr 2004 |
Note that you cannot specify the same field twice in the macro. If your linkage field is also a data field to be moved, it must be copied first. Default: Blank.
AO Compile Master Target tab
Master record flag
Optional. Records that are treated as the master (by virtue of order) are marked as "Y", others are marked as "N". This output is mostly for debugging purposes so that you can verify the record treated as master is correct. Default: COMPILE_MASTER_FLAG.
Field operations
Field | Description |
|---|---|
Field 1 | First field to process. This is both an input and an output. Default: Blank. Field 1 operation Total (numeric fields only): Total values in each group. Average (numeric fields only): Average of values in each group. Minimum: Lowest non-null value in group. Maximum: Highest value in group. Standard Deviation (numeric fields only): Generate standard deviation of values in each group. Variance (numeric fields only): Generates variance of values in each group. Longest (variable length text fields only): Longest text string in each group. First valid: First non-error/null value in group. Last valid: Last non-error/null value in each group. Concatenate (text fields only): Combines all values of a field into one, for example, a group of three values "A", "B", and "C" would be combined into "ABC". You must ensure that your field is long enough to hold the combined results or it will be truncated. Use a Change Field Types tool to lengthen a text field. Most frequent: Chooses the most frequent non-null value of each group. Multi-field: Choose a record based on the Multi-field linkage field and Multi-field operation defined on the Source tab, and use the field from that record. See Data Management's documentation on the Summarize tool for additional detail on these parameters. Default: Blank. |
Field 1 priority | Optional. For any operation except multi-field linkage, you can select a priority field that specifies the search order within the group that is used when looking for values. This is especially important if you select the First_Valid operation. Default: Group Order. |
Field N. . . | Up to 10 fields, operations, and priorities. |
Configure AO Compile Master
To configure AO Compile Master
Select the AO Associate Match IDs icon, and then select the Source tab on the Properties pane.
Select Match ID and select the field containing the match group ID.
Select Group order and select the field containing match group record order (a sequential number from 1 to N for each member of the group).
Optionally, select Multi-field link, Multi-field operation, and Multi-field priority to define how to copy multiple fields to the master record.
Select the Target tab, and then select fields and operations for output to the master record.
Optionally, go to the Execution tab, and then set Web service options.