#285: HbA1c and other laboratory data: data quality check, CDM ETL
----------------------------------+-----------------------------------
Reporter: dconnolly | Owner: mprittie
Type: enhancement | Status: assigned
Priority: major | Milestone: bariatric-study-data
Component: data-stds | Resolution:
Keywords: data-quality cdm-etl | Blocked By: 158
Blocking: 241 |
----------------------------------+-----------------------------------
Comment (by mprittie):
Unfortunately I didn't keep good notes here on my first pass several weeks
ago and I had to spend a couple of hours today retracing my (and Nathan's)
steps. I had originally tried to duplicate (with LOINC codes) the
approach Nathan had used with ICD9 code, where he replaced the SCHILS
hierarchies, in `PCORNET_DIAG` and `PCORNET_PROC`, with our local HERON
hierarchies.
This, unfortunately, did not work as the `PCORNetLoaster_ora.sql` joins
the `PCORNET_LAB` table on itself expecting a "parent" node which has a
`c_basecode` which is `PMN_LABNORMAL` (a table created by
`PCORNetLoaster_ora.sql`) which is also joined into the transformation
query.
{{{
select * from pcornet_lab where c_basecode like 'LAB_NAME:%';
}}}
... will return a list of the 12 "parent" nodes whose `c_basecode`s are in
the list of `LAB_NAME`s in `PMN_LABNORMAL1` (there appears to be a one-to-
one association between the rows in the result set and the rows in
`PMN_LABNORMAL1`).
Below is a select statement based on the relevant portion of the
`LAB_RESULT_CM` transformation. It retrieves all of the nodes which have
a "parent" node from the result set above. There are 177 such nodes in
the SCHILS hierarchy, many of which are to be duplicates with nothing
different than the `c_name` (48 disticnt `c_basecode`s):
{{{
select lab.* from pcornet_lab lab
inner JOIN pcornet_lab ont_parent on lab.c_path=ont_parent.c_fullname
inner join pmn_labnormal norm on ont_parent.c_basecode=norm.LAB_NAME
where lab.c_fullname like '\PCORI\LAB_RESULT_CM\%';
}}}
My current goal is to find a meaningful way to hang our local LOINC lab
leaves under the SCHILS "parent" nodes.
--
Ticket URL:
<http://informatics.gpcnetwork.org/trac/Project/ticket/285#comment:8>
gpc-informatics <http://informatics.gpcnetwork.org/>
Greater Plains Network - Informatics
_______________________________________________
Gpc-dev mailing list
[email protected]
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
