Home > Databases > Screen Data > Screen Data

Screen Data

The Screen Data command allows you to set up and run data screening tests according to your data's requirements. Screening options include checks for invalid values within a single database, as well as tests that compare current data values with those in a prior year's database. ProVal provides default tests in both of these categories so that, with minimal coding, you can perform a fairly extensive degree of data screening. You can also code custom screening tests shaped specifically to your data's needs and include them in your Screen Data parameters.

The first dialog box displayed after you select the Screen Data command is the library of all entries previously defined and unhidden in the current Project, if any such entries exist. To edit or use an entry, click its name. To set up a new set of Screen Data parameters, click New.

Name is a descriptive phrase used to identify the Screen Data library entry.

Select a topic to edit provides access to the areas for which parameters may be coded. Data and Key Fields are the only topics for which coding is required. (Note that these topics must be coded even if you are using only Custom Screening Tests in your data screening.) Coding of additional topics enables ProVal to screen your data more extensively. The topics do not vary by mode, although some of ProVal's default screening tests are mode-specific. Click a topic to access its parameters. Each topic is discussed below.

Click the Run button to execute the Screen Data tests. When the Screen Data testing is complete, ProVal displays the Output, which will report the Inputs used and the results of the screening tests processed. Each selected test will be included in the report, regardless of whether errors are found. When you view the output, you will also have the option of writing the errors to the Error Log for the Current Year database, merging or replacing with any errors previously logged.

 

Data

The fields in the Data topic indicate to ProVal the Current Year data to be screened, and, optionally, the Prior Year data to be used in comparison screening. Data for each year is identified by a Database and Valuation date; it is further defined by the selection of Census Specifications. Note that the Current Year data identification (Database and Valuation Date) must be complete in order for a Screen Data library entry to run, even if only Custom Tests are included in that library entry. Additional requirements for coding the Data topic are indicated in the pertinent sections of Help below.

The Project selected at the bottom of the dialog box determines whether the built-in tests for “Missing or invalid current year data” and “Unexpected changes from prior year” will be for pension or OPEB plans. It also determines the choices available for current and prior year Census Specifications.

Current Year (left half of the screen):

The Current Year data identification (Database and Valuation Date) must be complete in order for a Screen Data library entry to run. (That information must be complete even if the data screening to be performed consists solely of Custom tests.) To include any of ProVal's default tests to screen the Current Year data, you must also select Census Specifications.

Select the Database to be screened and indicate the applicable Valuation date.

Select the Census Specifications that you want ProVal to use to determine which database fields define status, birth, hire and other information involved in the screening process. In the absence of these Census Specifications, ProVal will not process any of the default screening tests. If you intend to process only Custom tests, Current Year Census Specifications need not be selected. Select from the list of Census Specifications already defined in the current Project or, to create a new set of Census Specifications, or modify an existing one, click the button.

If the Census Specifications set you select includes Data Defaults, then the Apply Data Defaults check box will be accessible, to offer you the indicated option. (You might choose to have the initial screening of your Database performed without the use of Data Defaults so that the extent of the data problems will not be obscured. For final screening, you may want to apply the Data Defaults so that you can assess whether your data is ready for valuation or other processing.)

A Selection Expression is optional. You can type an expression into the Selection Expression field, or you may use the arrow to the right of the field to access the Selection Expression library. You might choose to include an expression to limit the Current Year population to a particular group for screening purposes. If, however, you want to include comparisons of current and prior data in your screening process, you need to consider whether limiting the current population to the records that meet the Selection Expression criteria will result in a false "non-match" condition for some records in your prior data.

Prior Year (right half of the screen):

Including comparisons of current and prior data in the Screen Data process is optional. To include comparison screening, use the Prior Year fields to specify the prior Database to be used, as well as the Valuation Date and Census Specifications applicable to that data. Note that the Current and Prior Valuation Dates need not be one year apart; the only requirement for these dates is that the Prior Valuation Date must be earlier than the Current Valuation Date.

Although inclusion of Prior Year information is optional, if a Prior Year Database has been selected, you must provide the (prior) Valuation Date and, if you wish to run any built-in tests (i.e., any test besides your own Custom Screening Tests), you must also provide the Census Specifications associated with the prior year valuation. (If you intend to draw on the Prior Year data only for Custom Screening Tests, Prior Year Census Specifications need not be selected.) Select from the list of Census Specifications already defined in the current Project or, to create a new set of Census Specifications, or modify an existing one, click the button. To proceed without doing comparison testing, use the Database selection "<none>" at the top of the Database dropdown list box.

If the Census Specifications set you select includes Data Defaults, then the Apply Data Defaults check box will be accessible, to offer you the indicated option. (You might choose to have the initial screening of your Database performed without the use of Data Defaults, so that the extent of the data problems will not be obscured. For final screening, you may want to apply the Data Defaults so that you can assess whether your data is ready for valuation or other processing.) Note that the option to default numeric fields to zero (except salaries) will be ignored.

Selection Expression is optional. You can enter an expression as described above for Current Year data. Use of a Selection Expression will limit the Prior Year population that is available for comparison with the Current Year data that is screened. In some situations, limiting the Prior Year population to a particular group will suit your purposes. You need to consider, however, whether excluding a portion of the Prior Year population will distort the results of the data screening by preventing ProVal from finding matches for some of the Current Year records you are screening.

 

Key Fields

Completion of this topic is required to run Screen Data. From among the database fields unhidden in the current Project, select one or more "key" fields whose value(s) will uniquely identify each record ProVal will encounter in the Screen Data process. In addition, if your screening includes comparison tests, ProVal will use the values in these Key Fields to match current and prior data records.

If a key field you select is of the character type, the Match case in keys box is accessible, so that you can indicate whether you want ProVal to regard upper and lower case letters as distinct.

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 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. 

When the Screen Data process is run, ProVal will check for duplicate key values within the Current Year Database coded in the Data topic. If a Prior Year Database is also specified under the Data topic, ProVal will check for duplicate keys within that database. If duplicates are found, ProVal will display a message indicating that and processing will end. Use the Find Duplicates command on the Screen Data menu to determine what records have duplicate values.

 

Missing or invalid current year data

The list of Census Specification Screening Tests displays ProVal's built-in current year tests for missing or invalid data values. The tests cover all of the data fields referenced in Census Specifications. (In the absence of Current Year Census Specifications in the Data topic, ProVal will bypass these tests.) The Project selected under the Data topic determines whether the list will include OPEB-specific or pension-specific tests, along with the tests that apply to both types of plans. With the exception of the check for a valid status code, tests for records with inactive statuses are distinct from those for active records. Note that records whose status is mapped to the "Vested valued through active" status are screened by means of the tests applicable to active records, because it is those active data requirements that must be met in order for the records to be valued by ProVal. Custom Tests (see the discussion in a following section of this article) can be used to address any additional data screening requirements for the vested-as-active status (for example, to test for an unexpected change in the annual benefit amount).

Uncheck boxes for tests that you do not want ProVal to apply. You do not need to uncheck boxes for tests that ProVal will recognize as being irrelevant according to the Census Specifications coding. (For example, if the inactive payment forms in your Census Specifications do not include temporary benefits, ProVal will skip the screening tests related to "temporary benefit stop date", even if those tests remain checked.) ProVal will also skip tests on a per-record basis if the database items are irrelevant to the record being screened. (For example, ProVal will not apply the checks for valid J&S percentages to an inactive record whose payment form is a life annuity.)

To change the Error / Warning categorization of a test, or to key in a Custom Description for a test, click the Edit button to the right of the checklist. (The Error or Warning distinction has no intrinsic meaning to ProVal beyond the purpose of helping you to gauge the seriousness of data problems encountered, and the term "error" is sometimes used in the broad sense, encompassing both categories.) Note that ProVal will use your Error or Warning categorization labels, as well as your Custom Descriptions, in Screen Data output.

The Plan Definition, Funding Assumptions, and Accounting Assumptions dropdown list boxes provide the option to extend ProVal's current year data screening to include any data fields referenced in those areas of your coding. (The Plan Definition and Assumptions library entries shown in the dropdowns are determined by the Project specified under the Data topic.) Although it is optional, selection of these parameters is recommended for a more complete data screening process. In broad terms, the screening of database fields for Plan Definitions and Assumptions libraries parallels the validation of those library entries that occurs in conjunction with Valuation and other Execute menu processing. Specific screening tests performed (where relevant) will catch errors such as these:

 

Unexpected changes from prior year

The default tests under this topic involve comparisons of current and prior data. They are designed to alert you to changes that are generally not expected (e.g., a change in the date of hire for a continuing active record). Tests in this area are, by default, designated as Warnings. As with the default tests for missing or invalid current year data, tests for actives and inactives are distinct, and it is the active tests that are applied to records whose status code is mapped to the "Vested valued through active" status.

Uncheck boxes for tests that you do not want ProVal to apply. You do not need to uncheck boxes for tests that ProVal will recognize as being irrelevant according to the Census Specifications coding. (In the absence of Current Year and/or Prior Year Census Specifications in the Data topic, ProVal will bypass these tests.)

In the pension modes, the screening for unexpected changes can include inactive benefit-related database fields if ProVal determines that such a comparison would be meaningful. (This screening is not applied to records whose status code is mapped to the "Vested valued through active" status; Custom Tests can be used if such screening is desirable for your vested-as-active data.) ProVal evaluates the comparability by examining the Inactive Benefits coding in the Current Year and Prior Year Census Specifications. ProVal can compare the values of the database fields referenced by the coding even if that coding is not identical, as long as the number of Inactive Benefits is the same each year and the payment forms associated with the benefits are comparable. ProVal regards payment form definitions as being comparable if they involve (a) the same definition method (i.e., Single Payment Form both years, or Coded Field both years, although the coded database field, itself, need not be the same both years), and (b) the same payment form type (e.g., certain and life both years, or, if a coded field is used to determine payment form, the same codes used for the same payment forms in both years). Note that it is not necessary for all of the payment form parameters to be identical. For example, if the payment form type is Joint Life Annuity, but the Current Year and Prior Year definitions reference different database fields for the fraction of benefit payable when only the beneficiary survives, ProVal will still regard the related benefits as having the same payment form type. Note that if ProVal does not have sufficient information to compare inactive benefit amounts, it will not compare any of the inactive benefit-related database fields. Under those circumstances, Custom Tests can be used to compare database fields that ProVal does not screen.

To change the Error / Warning categorization of a test, or to key in a Custom Description for a test, click the Edit button to the right of the checklist. (The Error or Warning distinction has no intrinsic meaning to ProVal beyond the purpose of helping you to gauge the seriousness of data problems encountered, and the term "error" is sometimes used in the broad sense, encompassing both categories.) Note that ProVal will use your Error or Warning categorization labels, as well as your Custom Descriptions, in Screen Data output.

 

Status Changes

The Status Changes topic is accessible only if your Data topic coding includes Prior Year data information and the Census Specifications for the current and prior years use the same status field.

Check the box to Screen illogical status changes. ProVal provides a grid, based on the status field in the Census Specifications selected under the Data topic, through which you can identify illogical or unexpected status change combinations for ProVal to trap as potential errors. If you click the Populate button, ProVal will use information from the status code mapping in the Census Specifications to enter its "best guess" as to which cells represent illogical combinations. (For this purpose, ProVal categorizes the "Vested valued through active" status as it would the “Vested” status. Thus, for example, it will regard a status change from "Vested valued through active" to “Active” as being illogical, just as it would a status change from “Vested” to “Active”.) You can revise the entries, if necessary, by double-clicking to clear a cell that has been filled or to fill additional cells. (To clear a large area quickly, highlight its cells and press the Delete key.)

Note that as long as every other screen data test is turned off, ProVal will run the Screen illogical status changes test without validating census specifications.  This is useful if preparing the Form 8955-SSA or as a first step in the screen data process.

If a record from the Prior Year Database is absent (i.e., does not have a match) in the Current Year Database, and if that absence is to be considered an error, according to the specifications for Screen illogical status changes, the error information is associated with the Prior Year Database and can be written to its Error Log.

Check the Missing Vested Terminations box to find actives from the prior year who terminated, appeared to be vested, but are not among the current year’s inactive participants. The Service field and years parameters provide the additional screening criteria to be used.

Check the Surprise New Entrants box to find current year actives who are not among the prior year's eligible actives, but whose service would indicate that perhaps they should have been. The Service field and years parameters provide the additional screening criteria to be used.

The Error / Warning dropdown list boxes allow you to choose how test failures will be labeled. (The Error or Warning distinction has no intrinsic meaning to ProVal beyond the purpose of helping you to gauge the seriousness of data problems encountered, and the term "error" is sometimes used in the broad sense, encompassing both categories.)

 

Salary Changes

The Salary Changes topic is accessible only if your Data coding includes Prior Year data information.

Check the Screen Salary box to include salary comparisons in your screening tests. Although you may specify parameters once the box is checked, the related tests will be included in a screening run only if the coding of the Data topic contains complete data information (i.e., Database, Valuation Date and Census Specifications) for both the Current Year and Prior Year. Each of the two available comparison tests is initially labeled as a warning, but you can use the dropdown list boxes to change the label to Error, if you choose.

For the comparison of current and prior valuation salaries, parameters are required to identify the acceptable range of annual change in Salary. For example, if anywhere from a 5% decrease per year in Salary to a 10% increase per year is within the acceptable range, the lower and upper limits defining the acceptable range are coded as -5 and 10, respectively.

To identify the current and prior database salary fields to be compared, ProVal refers to the Salary Definitions in the Current Year and Prior Year Census Specifications. If a Salary Definition indicates that salary is to be imputed, the salary that ProVal will use in the comparison is the first (i.e., most recent) historical salary field in that year's database. Otherwise, ProVal will use the database field specified as the Current salary field in the Salary Definition for that year. Note that the Salary Definition used in the Prior Year's Census Specifications need not be the same as that used for the Current Year. If Salary is imputed in one Salary Definition but not the other, ProVal will adjust the comparison test accordingly, as described in the next paragraph. If the Census Specifications specify the Salary as the sum of multiple Salary Definitions, either all or none of those Salary Definitions should impute current salary based on historical salary. In addition, the current and prior year Census Specifications must be consistent in this regard. In the case of imputed salaries, the first historical salary in the current database will be compared to the first historical salary in the prior database. If instead a database field is selected for the Current salary field for the current and prior databases, the values in the selected fields will be compared. In either case, any Custom limit specified for a Salary Definition will be ignored for purposes of this data screening test.

To determine whether the annualized difference between the two salaries falls in the range you specify as acceptable, ProVal uses information from each Salary Definition, coupled with the time span between the current and prior valuation dates, and adjusts the range accordingly. In many cases, the salaries to be compared will correlate with a time span of one year, and the lower and upper limits will be used exactly as input. In some cases, ProVal will need to adjust the range to reflect Current and Prior valuation dates that are more or less than a year apart, and/or it will adjust because one salary is imputed and the other is not. Suppose, for example, that the coding for the salary comparison topic indicates that a range of + 5% per year is acceptable. If each Salary Definition specifies a database field for the Current Salary in the respective data, but the current and prior valuation dates are two years apart, ProVal will expand the acceptable range for the difference between the field values to + 10.25% (derived as 1.05 x 1.05, =1.1025, minus 1). If all of the facts are the same as in the last example except that the Current Year’s Salary Definition indicates salaries are to be imputed, ProVal would be comparing values in the Current Year data's most recent salary history field (i.e., salary for Valuation Date minus 1 year) with the Prior Year data's Current Salary (salary for Valuation Date minus 2 years) and, since those salaries would represent a time span of only one year, ProVal would use + 5% as the acceptable range.

Check the second box to include a comparison of historical salaries in the two databases to verify that there is no difference. The data fields used for this comparison are those identified as the Historical Salaries in the Current Year’s and Prior Year's respective Census Specifications. To align the historical salary fields to be compared, ProVal uses information from the Salary Definitions, in conjunction with the time span between the current and prior valuation dates. (ProVal does not use the field names for this information; thus, for example, it is not necessary for the database field names to indicate the years their respective salaries are associated with.)

 

Custom Tests

This topic enables you to include tests defined in the Custom Screening Tests library. Click the New button to create a new Custom Screening Test or click Add/Omit to select the existing Custom Screening Tests to use. Once they are included, Custom Screening Tests may be edited by double-clicking an entry or selecting an entry and clicking the Edit button. Note that the Data and Key Fields topics must have valid coding for custom tests to run. (This is true even if you are including only custom tests in your data screening.) Within the Data topic, the Database and Valuation Date for the Current Year must be coded. The Prior Year Database and Valuation Date are needed only if your custom screening tests include comparisons of current and prior data. Note that if a Custom Screening Test references database field(s) not in the corresponding database specified under the Data topic, ProVal will simply bypass that test, without interrupting Screen Data processing. (This handling facilitates establishment of a common set of Custom Screening Tests that can be used for multiple clients, even if some tests are not relevant for some of the clients.)

 

Output

This topic enables you to Select fields to be included in the Screen data output for any record that has an error or warning. Test components (e.g., Birth Date / Age) will be included in the output regardless of whether they are selected. Note that the Data Dictionary fields available for selection are those that are unhidden in the current Project.

Whenever current and prior year data have both been specified under the Data topic, ProVal will include a “flow of lives” grid in the Screen Data output, without regard to the coding (if any) for the Status Changes topic.

If the List RecIDs which had data defaults applied box is checked, ProVal will display the list of record IDs that had at least one data default applied.