Missing Value (MV) indicators allow individual data fields to be flagged if the original data is missing or suspect.
Out of range (OOR) indicators are used when data fields might contain textual indicators of being out of range, such as "<", ">", "<=", etc.
Note that mixing these two features for use on a single field is not recommended. Interactions between them may result in an error.
Missing Value Indicators
Missing Value (MV) indicators allow individual data fields to be flagged if the original data is missing or suspect. This marking may be done by hand or during data import using the MV indicators you define. Note that when you define MVIs, they will be applied to new data that is added; existing data will not be scanned, but missing values can be found using filtering and marked manually with the MVI.
Note: Missing value indicators are not supported for Assay Run fields.
Administrators can customize which MVI values are available at the site or folder level. If no custom MVIs are set for a folder, they will be inherited from their parent folder. If no custom values are set in any parent folders, then the MV values will be read from the site configuration.
Two customizable MV values are provided by default:
- Q: Data currently under quality control review.
- N: Data in this field has been marked as not usable.
Customize at the Site Level
The MVI values defined at the site level can be inherited or overridden in every folder.
- Select (Admin) > Site > Admin Console.
- Click Missing Value Indicators in the Configuration section.
- See the currently defined indicators, define new ones, and edit descriptions here.
- On older servers, the default descriptions may differ from those shown here.
Customization at the Folder Level
- Select (Admin) > Folder > Management.
- Click the Missing Values tab.
- The server defaults are shown - click the "Server default" text to edit site wide settings as described above.
- Uncheck Inherit settings to define a different set of MV indicators here. You will see the same UI as at the site level and can add new indicators and/or change the text descriptions here.
View Available Missing Value Indicators in the Schema Browser
To see the set of indicators available:
- Select (Admin) > Go To Module > Query.
- Open the core schema, then the MVIndicators table.
- Click View Data.
Enable Missing Value Indicators
To have indicators applied to a given field, use the "Advanced Settings" section of the
field editor:
- Open the field for editing using the icon.
- Click Advanced Settings.
- Check the box for Track reason for missing data values.
- Click Apply, then Finish to close the editor.
Mark Data with Missing Value Indicators
To indicate that a missing value indicator should be applied, edit the row. For each field tracking missing values, you'll see a
Missing Value Indicator dropdown. Select the desired value. Shown below, the "Hemoglobin" field tracks reasons for missing data. Whether there is a value in the entry field or not, the data owner can mark the field as "missing".
In the grid, users will see only the missing value indicator, and the cell will be marked with a red corner flag. Hover over the value for a tooltip of details, including the original value.
How Missing Value Indicators Work
Two additional columns stand behind any missing-value-enabled field. This allows LabKey Server to display the raw value, the missing value indicator or a composite of the two (the default).
One column contains the raw value for the field, or a blank if no value has been provided. The other contains the missing value indicator if an indicator has been assigned; otherwise it is blank. For example, an integer field that is missing-value-enabled may contain the number "12" in its raw column and "N" in its missing value indicator column.
A composite of these two columns is displayed for the field. If a missing value indicator has been assigned, it is displayed in place of the raw value. If no missing value indicator has been assigned, the raw value is displayed.
Normally the composite view is displayed in the grid, but you can also use
custom grid views to specifically select the display of the raw column or the indicator column. Check the box to "Show Hidden Fields"
- ColumnName: shows just the value if there's no MV indicator, or just the MV plus a red corner flag if there is. The tooltip shows the original value.
- ColumnNameMVIndicator (a hidden column): shows just the MV indicator, or null if there isn't one.
- ColumnNameRawValue (a hidden column): shows just the value itself, or null if there isn't one.
There is no need to mark a primary key field with a MV indicator, because a prohibition against NULL values is already built into the constraints for primary keys.
Out of Range (OOR) Indicators
Out of Range (OOR) indicators give you a way to display and work with values that are outside an acceptable range, when that acceptability is known at the time of import. For example, if you have a machine reading that is useful in a range from 10 to 90, you may not know or care if the value is 5 or 6, just know that it is out of range, and may be output by the machine as "<10".
Note that OOR Indicators are supported only for Datasets and General Assay Designs. They are not supported for Lists or Sample Types.
Enable OOR indicators by adding a string column whose name is formed from the name of your primary value column plus the suffix "OORIndicator". LabKey Server recognizes this syntax and adds two additional columns with the suffices "Number" and "In Range" giving you choices for display and processing of these values.
Open the
View Customizer to select the desired display options:
- ColumnName: Shows the out of range indicator (ColumnNameOORIndicator) and primary value (ColumnNameNumber) portions concatenated together ("<10") but sorts/filters on just the primary value portion.
- ColumnNameOORIndicator: Shows just the OOR indicator ("<").
- ColumnNameNumber: Shows just the primary value ("10"). The type of this column is the same as that of the original "ColumnName" column. It is best practice to use numeric values for fields using out of range indicators, but not required.
- ColumnNameInRange: Shows just the primary value, but only if there's no OOR indicator for that row, otherwise its value is null. The type of this column is the same as that of the original "ColumnName" column. This field variation lets you sort, filter, perform downstream analysis, and create reports or visualizations using only the "in range" values for the column.
For example, if your primary value column is an integer named "Reading" then add a second (String) column named "ReadingOORIndicator" to hold the OOR symbol or symbols, such as "<", ">", "<=", etc. :
Reading | ReadingOORIndicator |
---|
integer | string |
For example, if you insert the following data...
Reading | ReadingOORIndicator |
---|
5 | < |
22 | |
33 | |
99 | > |
It would be displayed as follows, assuming you show all four related columns:
Reading | ReadingOORIndicator | ReadingNumber | ReadingInRange |
---|
<5 | < | 5 | |
22 | | 22 | 22 |
33 | | 33 | 33 |
>99 | > | 99 | |
Note that while the "Reading" column in this example displays the concatenation of a string and an integer value, the type in that column remains the original integer type.
Related Topics