The Status Reconciliation command lets you analyze changes in values of any coded field contained on two selected files. Typically, the two files represent employee information for two different years and the coded field gives the employee status (such as active, retired, deceased, etc.). This command lets you find how many employees went from one status in the prior year to each possible status in the current year.

Status Field is the coded field (e.g., named "status") to reconcile between the two databases.

The Map button lets you:

• Map status field labels to Reconciliation Statuses (natural statuses). Only the Reconciliation Statuses will be shown in the reports. Field labels that are given the same Reconciliation Status will be combined as one status in the status reconciliation.
• Exclude statuses from totals. For example, you might exclude non-participating statuses to get a participants-only reconciliation. This is similar to using a selection expression to exclude records, except that excluded records are broken down by Reconciliation Status. For example, in the flow of lives table, additions might be broken down by excluded statuses "ineligible" vs "absent" from the prior year. A given Reconciliation Status must either be included or excluded, but not both.

Key fields specify the fields to use in matching records between the two files. This is usually a field like social security number or employee id, but can be any set of fields, as long as they uniquely identify each record.

The Match case in key(s) option determines whether ProVal should, for example, treat “a” differently from “A” in character fields. For case-sensitive treatment, check the box.

Alt Key is an optional parameter that can be used to specify a secondary key field (or fields). When a matching record cannot be found based on the primary key field, a second check is performed using the alternate key. This can be helpful in situations where Social Security numbers or employee ID numbers are corrected from one year to the next. As an example, consider a current year database with primary key field EEID and alternate key field OLDEEID. When matching EEID in the current year database against the prior year database, a second step will be taken for any unmatched records. Those unmatched records in the current year will also have the alternate key field OLDEEID checked for possible matches against EEID in the prior year. 

The Database containing Prior Year Data is the first of the two databases to compare. Generally, this file contains the original or older data. If you do not wish to reconcile statuses for all records on the database, enter a Selection Expression. Any record excluded by a selection expression will not be tracked or reported.

The Database containing Current Year Data is the second of the two databases to compare. Generally, this file contains the updated or newer data. If you do not wish to reconcile statuses for all records on the database, enter a Selection Expression. Any record excluded by a selection expression will not be tracked or reported.

Clicking the button to the right of either Selection Expression accesses the Retrieve Selection Expression dialog box. Pick a selection expression to retrieve by clicking its name in the list of selection expressions in the current Project. You will return to the preceding dialog box and the selection expression will appear in the appropriate Selection Expression box.

Name may be any descriptive phrase, including spaces, under which to save your Status Reconciliation settings. ProVal will automatically load the most-recently loaded Status Reconciliation entry the next time you visit this command.

Click the Run button to generate the status reconciliation reports. The "Status Reconciliation" report is displayed showing three main tables, "Prior & current year total," "Status Reconciliation - flow of lives," and "Status Reconciliation - grid". Additional reports are shown for each status change. The ​Report Options button allows customization of the fields shown for these additional reports.