Background
The NIH has requested sites to submit all available COVID-19 data, in particular to the measurement table. We ask all sites to submit COVID related lab results and map them to the recommended standard codes, as complete as possible.
Metrics in this tab
- Total COVID measurement count
- COVID mapping issue count
- COVID mapping issue rate
COVID Mapping Issue Tracker (link)
Concept_ids Inclusion/Exclusion Criteria:
Inclusion: All measurement_concept_ids that are descendants of concept_id 756055.
Exclusions:
- concept_ids that represent an EHR order* instead of lab test, i.e. with vocabulary_id 'CPT4' or 'HCPCS'.
- concept 3015746 (Specimen source identified) is excluded because the definition is too general and include non-COVID lab tests.
*Note: For the pure EHR orders of COVID tests, if an associated lab test and result exists, you don't need to submit the EHR orders. Otherwise, you can submit the data with measurement_type_concept_id = 32833 (EHR Orders). The EHR Orders will not be included in the metrics calculation.
Query for identifying valid COVID concept_ids:
SELECT DISTINCT c.concept_id
FROM concept AS c
JOIN `concept_ancestor` AS ca
ON c.concept_id = ca.descendant_concept_id
WHERE ca.ancestor_concept_id = 756055
AND c.vocabulary_id NOT IN ('CPT4', 'HCPCS')
AND c.concept_id != 3015746 --exclude due to too general definition
AND c.standard_concept = 'S'
The three columns in the COVID Mapping Issue Tracker intend to give sites an intuitive view of their COVID related lab results' data quality. The first column, total COVID measurement count, counts all measurement rows that have a measurement_concept_id that meets the inclusion and exclusion criteria defined above. The "COVID mapping issue count" columns counts all measurement rows that are found in column #1 that have either null or 0 in value_as_concept_id field. The 3rd column serves to combine the information in the previous two columns and show it as an intuitive percentage (rate).
Recommended standard codes for mapping
| Value | OMOP code to use |
| Abnormal | 45878745 |
| Detected | 45877985 |
| Not Detected | 45880296 |
| Positive | 45884084 |
| Negative | 45878583 |
| Invalid | 46237613 |
| Not Tested | 45878602 |
| Nonreactive | 45884092 |
| Reactive | 45881802 |
| Inconclusive | 45877990 |
| Indeterminate | 45884091 |
| Presumptive positive | 36715206 |
| Uncertain | 45884430 |
| Comment | 4083205 |
**Please ensure casing is ignored (i.e. DETECTED is the same as detected) when mapping your source system values to these mappings.
SQL template
For identifying pure EHR orders
WITH
valid_covid_concepts AS (
SELECT distinct c.concept_id
FROM `concept` AS c
JOIN `concept_ancestor` AS ca ON c.concept_id = ca.descendant_concept_id
WHERE ca.ancestor_concept_id = 756055
AND c.vocabulary_id NOT IN ('CPT4', 'HCPCS')
AND c.standard_concept = 'S'
AND c.concept_id != 3015746 --exclude due to too general definition
)
For identifying COVID-19 related mapping
SELECT DISTINCT m.value_source_value, count(*) AS freq
FROM `measurement` AS m
WHERE m.measurement_type_concept_id != 32833 --exclude EHR orders
AND m.measurement_concept_id IN
(SELECT concept_id FROM valid_covid_concepts)
GROUP BY m.value_source_value
ORDER BY freq DESC;
For identifying COVID-19 mapping issue
SELECT DISTINCT m.value_source_value, count(*) AS freq
FROM `measurement` AS m
WHERE m.measurement_type_concept_id != 32833 --exclude EHR orders
AND (m.value_as_concept_id IS NULL OR m.value_as_concept_id =0)
AND m.measurement_concept_id IN
(SELECT concept_id FROM valid_covid_concepts)
GROUP BY m.value_source_value
ORDER BY freq DESC;
Features available on dashboard
- Tooltip - detailed information, calculation equation, and links to Zendesk articles by clicking the information button
: