This is how we do this kind of panel test.
First, load both order test and detailed test into Measurement table as shown below:
| Measurement table | |||||
|---|---|---|---|---|---|
| measurement_id | person_id | measurement_date | measurement_concept_id | value_as number | measurement_source_value |
| 222333 | 99999 | 11/21/2019 | 40761511 | 58410-2 | |
| 444555 | 99999 | 11/21/2019 | 3000963 | 14.5 | 718-7 |
| 666777 | 99999 | 11/21/2019 | 40760954 | 6.2 | 57845-0 |
| 888999 | 99999 | 11/21/2019 | 3026361 | 4.82 | 26453-1 |
Then link the Panel test name with detailed test in the Fact_Relationship table using relationship “Panel Contains” (46233678) as shown below:
| Fact_Relationship table | ||||
|---|---|---|---|---|
| DOMAIN_CONCEPT_ID_1 | FACT_ID_1 | DOMAIN_CONCEPT_ID_2 | FACT_ID_2 | RELATIONSHIP_CONCEPT_ID |
| 21 | 222333 | 21 | 444555 | 46233678 |
| 21 | 222333 | 21 | 666777 | 46233678 |
| 21 | 222333 | 21 | 888999 | 46233678 |
Some relevant reading is here