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.
Missing Value Indicators
Administrators can customize which MV values are available at the site or folder level. If no custom MV values 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: Required field marked by site as 'data not available'.
Customize at the Site Level
The MV 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.
Customization at the Folder Level
- Select (Admin) > Folder > Management.
- Click the Missing Values tab.
- The server defaults are shown - click them 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.
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.
How Missing Value Indicators Work
Two 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 "1" in its raw column and "Q" 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.
MV indicators render with three column choices:
- ColumnName: shows just the value if there's no MV indicator, or just the MV plus a little 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: 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".
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. :
For example, if you insert the following data...
It would be displayed as follows, assuming you show all four related columns:
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.