15 Lab Ranges

15.1.1 Defining Lab Units

You must define all acceptable units of measure for all lab test Questions in the Maintain Lab Units window.

If you collect data using different units whose value is exactly the same (for example, percentages and decimal values), you can group those units into synonym groups by giving them the same Representative Unit Code. To do this, you must define a unit with the same name you want to use as the Representative Unit Code. This Code must not be longer than ten characters, and should be meaningful because it is what you will see in other Oracle Clinical lab definition windows. When you save this unit definition, its value becomes available to other units in the Representative Unit Code field list of values.

You have the option of adding free form text to help in pronunciation. Lab unit pronunciation appears only on this form and on the Lab Units Report.

You must define Lab Units at the Global Library location. You can add a new unit at any time. You can change a unit only if it has not yet been saved, and delete one only if it is not referenced by a lab test Question, but you can choose to make a unit inactive at any time.

To define Lab Units:

1. Navigate to Labs, Lab Units, and then Lab Units. The Maintain Lab Units window opens.

2. In the Lab Unit Code field, define the name of the Lab Unit. For example, g/ml.

3. In the Lab Unit Name field, enter a descriptive name for the Lab Unit.

4. Enter a Representative Lab Unit Code. These codes enable you to define Lab Units as equivalent in the system. For example, because the Lab Units mg/ml and mg/cc are equivalent, you can define mg/ml as their common Representative Lab Unit Code. You can enter a code of up to 40 characters.

5. (Optional) Describe how this Lab Unit is pronounced in the Lab Unit Pronunciation field.

6. Select the Active? box to enable this Lab Unit to be used, or clear it to hide it from the system.

7. Save. Oracle Clinical commits this Lab Unit to the database.

15.1.2 Defining lab test Question Units

When you have defined all necessary Lab Test Units, associate each lab test Question you will be using with as many units of measure as apply, but at least one.

Lab units can be selected only by their representative Lab Unit code; however this code represents all the synonyms in the group—that is, all synonyms in the group are valid for the lab test Question.

In the Maintain lab test Question Units window you can insert representative Lab Units and delete those not referenced from Lab Unit conversions, ranges, Preferred Copy Groups, or preferred conversion groups. If the Lab Unit is referenced, you can choose to make it inactive. The Active? field controls whether the Lab Unit appears on lists throughout the Lab Ranges subsystem when Lab Units are called for. If a unit is marked inactive, it does not appear on lists.

To define lab test Question units:

1. Navigate to Labs, Lab Units, and LTQ Units. The Maintain lab test Question Units window opens.

2. Query for the lab test Question to which you want to associate units.

3. Define each representative Lab Unit name that you want to use as a unit measure for this Question, and either select the Active? box to enable this Lab Test Unit for the Question or clear it to render this unit inactive for this Question.

4. Save. Oracle Clinical enables you to use the representative Lab Test Unit or Units when you enter lab data for this Question.

15.1.3 Defining Unit Conversions

You must define a unit conversion for each Question that has more than one acceptable unit of measure. You can write a Derivation Procedure that references this conversion formula (see Chapter 16, "Validation and Derivation Procedures").

If your study collects lab data for the same Question in multiple unit types (for example, kilos in Europe and pounds in the U.S.), you can specify conversion formulas to ensure that the data can be analyzed and reported using a single unit of measure. Use the Lab Unit Conversions window to set up standard conversion definitions.

The Lab Unit conversion must be unique in both source (the From: Lab Unit Name) and destination (the To: Lab Unit Name); the system checks for uniqueness when you leave the Lab Unit conversion record.

Always specify the conversion TO your preferred unit of measure, the one you will use to analyze the data. Later, in the Maintain Preferred Conversion Groups window, you specify which unit is preferred for each Question.

The conversion must be expressed in terms of multiplication and/or addition. To divide, multiply by a fraction; to subtract, add a negative number. However, you cannot enter a multiplier of zero or a negative number; doing this results in an error message. The system issues warning messages in the following circumstances:

• The inverse of the reverse multiplier is not within 1/10000 of the new multiplier.

• Another multiplier for the same lab test Question is within 1/10000 of the new one.

To define a Lab Unit conversion:

1. Navigate to Labs, Lab Units, and LU Conversions. The Maintain Lab Unit Conversions window opens.

2. Query for the lab test Question for which you want to define conversions.

3. Query for a Lab Test Unit you want to use as the From value. You can only choose Lab Test Units that have been defined for this Question in the Maintain lab test Question Units window.

4. Query for the Lab Test Unit you want to use as the To value. In addition to being defined as a Lab Test Unit for this Question, you must also choose a different unit than you selected as the From value.

5. Choose a Conversion Source.

6. Enter the Multiply Number and/or Add Number for this unit conversion.

7. (Optional) Enter a comment that describes this conversion further.

8. Save. Oracle Clinical saves this Lab Unit conversion for this Question.

15.1.4 Defining Preferred Conversion Groups

A preferred conversion group is a set of lab test Questions and the preferred Lab Unit of measure for each Question. The preferred conversion group, together with the relevant Lab Unit conversion formulas, can be used by a Derivation Procedure to convert a response to the lab test Question collected in one unit of measure to a derived value in the preferred standard unit of measure (see Chapter 16, "Validation and Derivation Procedures").

1. Navigate to Labs, Lab Units, and Pref Conv Groups. The Maintain Preferred Conversion Groups window opens.

2. Define the unique group ID in the Group field. This ID can be up to 10 characters long, and you cannot change it once you save the group.

3. Define a Group Name.

4. (Optional) Enter a comment to describe supplementary information about this preferred conversion group.

5. Enter each Preferred Lab Unit for this group:

   1. Query for a lab test Question. When you choose one, Oracle Clinical populates its domain as well.

   2. Query for a Lab Unit Name associated with that Question.

   3. Enter the number of decimal places that you want reports displaying this value to use. Once you save, you cannot change this value.

   4. (Optional) Enter a comment with supplementary information about using this preferred Lab Unit for this group.

6. Save. Oracle Clinical commits this preferred conversion group to the database.

15.2.1 Defining Textbook Ranges

Textbook lab range definitions serve as the company standard range for lab test Questions. They provide a default definition for each lab test Question where no lab-specific ranges have been defined. You can also use these definitions as a foundation from which to create more specific lab range definitions for particular labs.

Oracle Clinical enforces uniqueness for textbook ranges in a similar way to lab ranges, but considers one more column: the Lab Unit Name. Thus, each textbook range must be a unique combination of lab test Question, Domain, Sex, Maximum Age, Minimum Age, Effective Start and End Date, and Lab Unit Name.

The Maintain Textbook Ranges window only displays the representative unit names. Representative Lab Test Units are defined when you define lab test Question units; see "Defining lab test Question Units".

You can use the Maintain Textbook Ranges window to delete textbook ranges; however, you cannot delete a textbook range if a Preferred Copy Group uses it.

To define a textbook range:

1. Navigate to Labs, then Labs, and Textbook Ranges. The Maintain Textbook Ranges window opens.

2. Query for the Question for which you want to establish textbook ranges. The system displays only Questions that have a Question type of Lab Test.

3. Enter each textbook range for this Question:

   1. Enter the gender to which this range applies. Valid choices are Male (M), Female (F), or Both (B).

   2. Enter the minimum and maximum age ranges. You can leave either end of the age range blank, if no minimum or maximum age applies for this range.

   3. Enter the low and high values for this textbook range, and the Lab Unit Code for the measurement you are specifying.

   4. (Optional) Enter the dates during which the textbook range is in effect. You can leave one or both of these blank.

4. Save. Oracle Clinical commits these textbook ranges to the database for this lab test Question.

15.2.2 Defining Preferred Copy Groups

A Preferred Copy Group is a named set of lab test Questions and the units of measure that each question uses. Using Preferred Copy Groups enables you to create and copy groups of textbook ranges; each time you add a lab test Question to a Preferred Copy Group and choose a preferred unit of measure, Oracle Clinical associates the textbook ranges you have defined for that Question with the copy group. When you copy textbook ranges into a lab definition, the Use Textbook Ranges window only allows you to use textbook ranges that are grouped into Preferred Copy Groups.

You can create Preferred Copy Groups to group Questions that often need to be copied together when applying ranges to a lab; or for lab test Questions that are identical except for, perhaps, the unit of measure measured.

Differences in units of measure arise when you have locations in Europe and also in the United States. To handle these differences you might have a Preferred Copy Group called US and one called EURO. For example, the Hematocrit test would likely be collected as a fraction in Europe and as a decimal in the U.S., which you would translate as two identical textbook range definitions for Hematocrit:

Hematocrit      Male        42-54            %              Female           38-46           %
Hematocrit          Male            .42-.54        fraction         Female          .38-.46     fraction

Preferred Copy Groups and Preferred Conversion Groups (from the Labs menu, select Lab Units, then Pref Conv Groups) take almost the same information about lab test Questions and their preferred units of measure, but are used for different things. Conversion groups are used by Derivation Procedures and copy groups are used to copy lab Questions and their preferred unit and textbook range for use in a new lab; see "Defining Preferred Copy Groups" and "Defining Preferred Conversion Groups".

To define a Preferred Copy Group:

1. Navigate to Labs, then Labs, and Pref Copy Group. The Maintain Preferred Copy Group window opens.

2. Enter a unique ID for this group. This ID can be up to 10 characters long.

3. Enter the name for this group. This name is required, but you can change it even after you save the Preferred Copy Group.

4. (Optional) Enter a comment with supplementary information about the Preferred Copy Group.

5. Enter each lab test Question and its preferred Lab Unit combination that you want to include in this Preferred Copy Group:

   1. Query for the lab test Question. When you choose one, the Maintain Preferred Copy Group window also populates its domain.

   2. Query for the Lab Test Unit you want to use for this Question.

   3. (Optional) Enter a supplementary comment about including this Question and unit in the Preferred Copy Group.

6. Save. Oracle Clinical commits this Preferred Copy Group to the database, making it available for use in the Use Textbook Ranges window.

15.2.3 Defining Lab Panels

To define a lab panel:

1. Navigate to Labs, then Labs, and Lab Panels. The Maintain Lab Panels window opens.

2. Define a unique identifier for the lab panel, not greater than 10 characters, in the Panel field.

3. Define a unique name for the lab panel, not greater than 60 characters, in the Panel Name field.

4. (Optional) Enter a comment to describe this lab panel. Descriptions in this field can be useful to other users browsing through lab panel records.

5. Enter the lab test Questions for this lab panel in the lower part of the window. For each lab test Question:

   1. Click in an empty row. The Maintain Lab Panels window populates the sequence number (Seq #) field with the next unused number.

   2. Enter a SAS Name for a Question defined and active in the Global Library with data type NUMBER and type LAB TEST.

      When you choose a Question, Oracle Clinical automatically populates the lab test Question and lab test Question Domain fields with the corresponding information for that SAS Name.

   3. Choose a lab test Question Type.

   4. (Optional) Enter a comment about including this lab test Question in the Lab Panel.

6. Save.

You can modify existing lab panels in a similar way. Query for the lab panel you want to change using the fields in the upper part of the window, and add, remove or change lab test Questions.

15.2.4 Defining Labs

This section describes how to define the basic lab settings using the Maintain Labs window. A Lab Definition consists of the necessary contact and related information, and the lab ranges to be applied to data from that lab. You have to define the information in this section before you establish the lab's ranges.

Use the Maintain Labs window to launch these tasks: define lab ranges and lab range subsets, use textbook ranges in the lab definition, and copy lab ranges. See the following sections for each one of these topics:

• Defining Specific Lab Ranges

• Defining Lab Subsets

• Using Textbook Ranges

• Copying Lab Ranges

You can also delete labs using the Maintain Labs window; however, you can only delete a lab if it is not referenced by any received DCMs or lab assignment criteria.

To define a lab:

1. From the Labs menu, select Labs, then Labs. The Maintain Labs window opens.

2. Enter the Lab ID and its name. The ID must be no greater than 10 characters, all uppercase.

3. Select the Active? box to activate the lab, or clear it to render it inactive. Only active labs can supply lab ranges for the lab data entered or batch loaded for a study. You can change a lab's active status at any time.

4. Ranges Approved: If this field is checked, all ranges for this lab have a status of Approved. This is a display-only field.

5. Format Mask: Choose the default file format mask you want to use for batch loading data from this laboratory.

6. Owning Location: Name of the Oracle Clinical database that owns this lab data. This is a display-only field.

7. Replicate?: If checked, this lab must be replicated from an Oracle Clinical database other than your current database.You can change this flag from unchecked to checked at any time, but once checked, you cannot uncheck it.

8. Last Replication: Date and time this lab was last replicated from the Oracle Clinical owning location. This is a display-only field.

9. Enter the necessary information for the person who serves as the contact for this lab: the contact's first and last name, phone number and fax number.

10. Enter the lab's full mailing address.

11. Enter the start and end dates for this lab's accreditation.

12. Enter the certification agency and number for this lab, if applicable.

13. (Optional) Enter a supplementary comment to describe this lab.

14. Save. Oracle Clinical commits this lab definition to the database.

15.2.5 Defining Specific Lab Ranges

Use this window to specify ranges for this lab that are different from your company's textbook range for particular Questions.

As in textbook ranges, you can define a different range of valid values for each Question depending on the age or gender of the patient. Each range in the lab must have a unique combination of lab test Question, sex, maximum age, minimum age, and effective start and end date. If any range definition has a blank field for Lab Unit, low range, or high range, the range status is automatically set to MISSING, in which case the range is considered incomplete and not used for calculations.

The ranges you define here, and only the ranges you define here, will be available for Subsets definition (see "Defining Lab Subsets").

For a special use of the Ranges window, see "Using Textbook Ranges".

15.2.6 Defining Lab Subsets

Oracle Clinical enables you to create (optional) subsets of existing lab ranges, which you can use for particular lab Questions where the normal range is affected by a predictable factor. For example, diabetes consistently influences the range of data considered normal for certain Questions.

To prepare for subset creation, when you define a lab Oracle Clinical creates it with a single default subset. When you define ranges for a lab, Oracle Clinical automatically associates them with the default subset as well. These are the only ranges that are available to other subsets for the lab.

Before creating subsets you must enter the values you plan to use for subset names in the LAB RANGE SUBSET CODE installation reference codelist. If you define no explicit subsets then the lab will have only one, lab_name$default. The name assigned to the default is also specified in the LAB RANGE SUBSET CODE reference codelist.

You also define these lab range subsets in the Lab Range Subsets window. When you create a subset, you are required to select the Questions from a list of the Questions included in the default subset. The default subset is created automatically when the lab is created. Subsets have the syntax lab_name$subset_name when a lab is referenced elsewhere in the system.

15.2.7 Using Textbook Ranges

The List function in the pop-up window displays available copy groups. The New Dates Start/End field in the Use Textbook Ranges window substitutes for the original effective dates of each of the textbook ranges. If the textbook ranges have changed over time, the Text Range Date field lets you copy older textbook ranges.

You can copy a whole set of lab Questions, their preferred units of measure, and predefined company-standard textbook ranges (see "Defining Textbook Ranges") by clicking the Textbook Ranges button and entering the name of a Preferred Copy Group (see "Defining Preferred Copy Groups").

To use textbook ranges in a lab:

1. From the Maintain Labs window (from the Labs menu, select Labs, then Labs), choose the lab for which you want to use textbook ranges.

2. Click the Use Textbook Ranges button. The Use Text Book Ranges window opens.

3. Select a Preferred Copy Group to use. Oracle Clinical will copy this group's test Questions, ranges and units into this lab.

4. If you want to use the current version of the textbook ranges, leave the remaining fields empty and click OK.

   If you want to use earlier versions of the textbook ranges, enter a date when this textbook range was current in the Text Range Date field. You can only use previous versions of textbook ranges if these ranges are still active.

5. Enter the dates over which the copied ranges will be effective. Dates must be in the YYYYMMDD format.

6. Click OK to close the Use Text Book Ranges window, then save.

15.2.8 Copying Lab Ranges

If you are creating a new lab definition for a lab that will perform a set of lab tests very similar to an existing lab, you can copy a whole set of lab ranges from another lab definition by pressing the Copy Lab Ranges button and choosing a valid lab to copy from. You can then edit the ranges, if necessary. You cannot edit them once the new lab has been used.

To copy lab ranges from another lab into the lab you have selected:

1. In the Maintain Labs window (from the Labs menu, select Labs, then Labs), choose the lab into which you want to copy lab ranges.

2. Click the Copy Lab Ranges button. The Copy Lab Ranges window opens.

3. Choose the lab from which you want to copy ranges, and click Copy.

   The operation generates Alert messages, which list the number of ranges copied, number rejected, and which Questions were not copied because of a conflict. Lab ranges that copy successfully will appear in the selected lab with a Range Type of LAB and a Comment of COPIED.

4. Save.

15.3.1 Example of Lab Assignment Criteria Evolution

The following table illustrates how lab assignment criteria may evolve. Each line represents the creation of a new LAC in the same study. Each line is annotated below the table with the events that prompted the definition of a new LAC.

1. The original LAC, created when the study began: Investigator V1 managed all patients and Lab L1 processed all lab tests.

2. A new site, S2, is added to the study. Lab L2 processes its lab tests.

3. The original Investigator, V1, is now working with a different lab, L3. Any RDCMs that come in between December 2, 1997 and January 15, 1998 are assigned to L3. If no new LAC is defined for the period following January 15, then assignments default back to the LAC on line 1, which has no date.

4. During the period when V1 is using a different lab, one of his patients, P1, continues to use the original lab, L1.

15.3.2 Applying Lab Assignment Criteria

In addition to storing the lab to which each lab test RDCM is assigned, Oracle Clinical stores the method by which the lab was assigned. These methods are hierarchical and you can use this hierarchy to force Oracle Clinical to reassign certain RDCMs. The following table describes the five methods in order of descending precedence.

To apply Lab Assignment Criteria to a study:

1. Navigate to Labs, and Lab Assignment Criteria. The Maintain Lab Assignment Criteria window opens.

2. Query for, then select, the study to which you want to apply LACs.

3. Click Apply LAC. The Apply LAC to Study window opens.

4. Enter a Lab Assignment Type. This selection forces a batch process to examine each lab RDCM for the study. Any that have a lab assignment type equal to or lower in precedence that the method you entered is re-evaluated and reassigned according to the current lab assignment criteria.

5. Enter a Change Reason. A list of values is available.

6. (Optional) Enter an Audit Comment about applying this LAC to this study.

7. (Optional) Select the Process Locked RDCMs box to include locked RDCMs as well. This box may be grayed out if you only have update privileges, because processing locked RDCMs requires Privileged Update. See "Granting Privileged Update to a User" for instructions on being granted Privileged Update.

8. Click Apply. Oracle Clinical applies the LAC to this study.

15.3.3 Resolving Problems

Lab data may not link to ranges because an RDCM is not linked to a lab or because necessary patient information, such as age or gender, is missing. These problems—Missing Lab References in the RDCMs and Missing Data in Patient Positions—can be identified in windows reached through the buttons in the Maintain Lab Assignment Criteria window.

15.4.1 Loading Data

The goal of the data load stage is to verify that a value is present for each mandatory key column. There are no checks for the validity of the data at this stage; only that it exists.

The system loads the data into a temporary table, TEMP_RANGES. If there are existing rows for the lab being loaded, then it aborts and displays a message, "Another Batch Data Load in Progress for this lab <Lab Name>." The input to the process is the lab name, the full pathname to the data file, the domain name, and the delimiters used for the data, which are entered using a form described in detail in "Executing the Process".

After all validations are complete, the entire data file is loaded into TEMP_RANGES. If there are errors, the status code in the LAB_DATA_FILES table is updated to BAD LOAD, but rows without errors are inserted in TEMP_RANGES.

CENTRAL,,SODIUM,F,10,20,-5,10,u/ml,,,DAYS,DAYS

15.4.2 Validating Data

During preparation stage the system performs the following validations on the data loaded into the temporary table:

• Checks input data against existing database definitions for duplicate rows for:

• • Lab Name

  • Question Name

  • Lab Unit

• Performs lab range validations to check:

  • Relative values of the age fields—maximum_age should be greater than the minimum_age

  • Overlap in gender fields

  • Date range; with the last row having an open Effective End Date

  • minimum_value and maximum_value

    • These parameters can be zero, a positive value, or a negative value.

    • maximum_value should be greater than minimum_value, if both the values are not null.

After the error checking, the system produces a printable report of the results of the load and prepare stages, indicating the number of erroneous records and providing the PSUB job ID, which can be queried in the Batch Jobs window.

15.4.3 Transferring Data

Following checks of the TEMP_RANGES table for validation errors and duplicate rows, the permanent lab ranges database tables are populated from the rows in the TEMP_RANGES table. If no duplicate rows exist then all the rows from the TEMP_RANGES are inserted into the RANGES table. Duplicate rows are handled as follows:

• If you have the same lab range (combinations of key values, i.e. Lab, Question, Gender, minimum_age, maximum_age) and different minimum value or maximum value, update the record with the new minimum or maximum value, or both.

• If the audit has to be recorded in the range_comment column, then the values of previous values and the updated values can be shown in the comment.

• If the audit is not being recorded in the range_comment column, then the range_comment can also have a different value and can be updated.

• If you have the same key combinations (Lab, Question, Gender, minimum_age, maximum_age) and different Effective Start and End Dates, rearrange data according to the rules to accommodate the new effective dates.

• If your date ranges overlap in a record, rearrange all rows for that key set.

15.4.4 Executing the Process

You set parameters and execute batch data loads of lab range data from within Oracle Clinical. To execute a batch data load:

1. From the Labs menu, select Lab Batch Data Load, and Lab Ranges Batch Data Load.

2. Complete the fields as follows:

   Run Error Check Only (Y/N)? specifies whether to run a preliminary error check on the file (Y) or execute the full batch load (N).

   Lab Data File Name provides the full pathname to the data file. The file must be in the directory specified in the LAB_ROOT setting of the OCL_STATE local reference codelist. If LAB_USERDIRS in the same codelist is set to Y, copy the file into the user-specific subdirectory for the user is submitting the job. See the Oracle Clinical Administrator's Guide for more information.

   Column Delimiter used in the Data File identifies the delimiter used in the file to separate data elements. There are no restrictions in choosing this delimiter.

   Domain Name identifies the domain of the lab test Questions.

   Default Effective Dates (Y/N)? specifies whether the program should set the effective dates (Y) or if you plan to specify them manually in Oracle Clinical (N).

3. Click Submit Job.

The program produces a detailed output file, providing a complete listing of the events that occurred during execution. If the process completes successfully, it places a new row in the RANGES table.

15.4.5 Running the Lab Procedures Re-execution Pre-Process Batch Job

If you change any part of a lab range definition that has been used against production data, you must immediately execute the PSUB job by selecting Lab Procedures Re-Execution Pre-Process from the Lab menu. If you do not run this job, your changes will not be applied to existing data.