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

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.

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.
Go to the Source tab on the Properties pane.
Select Match ID and choose the field containing the match group ID.
Select Group order and choose 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.