The Merge Individual Aggregate command is used for valuations performed under the individual aggregate cost method (either % of salary or level $), as specified in the Asset & Funding Policy. When the cost method is individual aggregate, the Valuation and Valuation Set automatically store the calculation results needed to allocate assets for each record for the next year’s valuation. The Merge Individual Aggregate command merges these results into a database to be used for the next year’s valuation. This database generally would be a copy of the current year’s database file, and new values for variables needed to allocate assets would replace the values, if any, used in the current year’s valuation. For more information, see the discussion of individual aggregate asset allocation under the Liability Methods topic of Valuation Assumptions.

Before executing the Merge Individual Aggregate command, open the database file that you intend to use as census data for next year’s valuation.

First, select a Valuation Set with individual aggregate results whose calculation results you wish to place on the database for next year's valuation. Note that the target database has been pre-populated with the currently open database file; ProVal will merge the valuation set results into this file. Also note that you cannot choose a valuation set that includes a valuation specifying the currently open database as its census data file. Such sets have been removed from the selection choices.

Next select one or more key fields from the list of all fields in the current database. If you choose a character field, the match case in keys check box becomes accessible. The default is to ignore alphabetic case differences when matching key values (so, for example, “Joe Smith” matches “JOE SMITH”). Check this box to treat case differences as significant (so “Joe Smith” and “JOE SMITH” will be treated as different key values).

Select one or more source fields to read (to import into the target database) from the list of all fields in the source database. Click the All or None buttons to select all or none of the fields, respectively. By default, fields in the valuation set are used to update identically named fields in the current database. (Or, if you are importing new fields, the fields created in the database will have the same name as the fields in the update file.) To choose different names for the imported fields, click the Target Flds… button.

Select one or more fields to merge from the list of all output fields produced for each record by this year’s valuation set. An asterisk marks fields that already exist in the database being prepared for next year. (Fields without the asterisk are not contained in this database and will be initialized with missing values before the merge process begins.) Click the All or None buttons to select all or none of the fields, respectively.

The Target Fields dialog box lists the fields you’ve chosen to import (in the spreadsheet column Field in Source Database). In addition, the fields in the target database into which these fields will be mapped are listed. By default, these fields are listed in the second column, representing field names that are Existing Fields (i.e., already exist in ProVal's Data Dictionary). Alternately, you can manually choose to map into a New Field in the target database (i.e., a field that is new to ProVal's Data Dictionary).

The last onscreen options dictate how the data update should be performed. The first option specifies what should be done for records that were found in both files (i.e., records that were "matched" based on value(s) of the key field(s)). Select, for records found in both files, if you want to overwrite:

• All values. Data from the valuation set will REPLACE existing data in the target database, with one exception: missing values in the update file will never be used to replace non-missing data in the database. That is, existing (i.e., non-missing) source data will always overwrite target database data.
• Only missing values. Existing non-missing values in the database will not be altered, but any "holes" in the database (i.e., missing values) will be filled in with data from the valuation set.
• Nothing. Neither missing nor non-missing data in the target database will be updated (i.e., records that already exist in the target database will be unchanged). This option is uncommon but could be useful when you are using the merge process only to add new fields or records to the database.

The second option specifies what should be done with the records themselves (i.e., what records should be preserved in the merged target database). Select, in the merged target database, whether you want to keep records if present in:

• The target database. Records found ONLY in the valuation set will be ignored, and records present in the target database but NOT in the valuation set will remain in the target database.
• The source database.  Records found ONLY in the valuation set will be appended to the target database, and records present in the target database but NOT in the valuation set will be deleted.
• Both databases. Records found ONLY in the valuation set will be ignored, and records present in the target database but NOT in the valuation set will be deleted.
• Either database. Records found ONLY in the valuation set will be appended to the target database, and records present in the target database but NOT in the valuation set will remain in the target database.

Lastly, the option to Create MergeStatus field allows you to specify whether you want a "merge status" field in the database. This coded field, which will be named MergeStatus, classifies each record as one of:

• Matched: found in both the target and source databases
• Not Matched: found only in the target database, or
• Appended: found only in the source database and added to the target database

Click the Run button to perform the merge. When the merge is complete, the system displays a summary of what was done and asks for confirmation before saving the changes in the file. The summary includes the following information:

• Number of records in the database.
• Number of records in the source database and, if you provided a selection expression, the number of selected records.
• Number of records found in both files (i.e., matched records).
• Number of records updated for each field. This is the number of records for which the source database contained data different from that in the target database.
• Number of records found only in the source database, and the number that were appended to the target database. Only records satisfying the selection expression are counted.
• Number of records found only in the target database, and the number that were deleted.

Click “Yes” to save the changes, or click “No” to discard the changes and return to the Merge Individual Aggregate parameters. If you choose “No”, the merge will be canceled, but any new fields that were created (and filled with missing values) during the merge will still exist. You can erase these fields, if you wish, by using the Database > Edit Data > Delete Fields command. (Note: The Merge Individual Aggregate command produces large amounts of unused space in the database file, even if you choose not to save the changes made in the update. You can eliminate the unused space by running the File > Pack Files command.)