Opened 5 years ago

Closed 2 years ago

Last modified 23 months ago

#178 closed enhancement (fixed)

Query for encounter vitals in HERON

Reported by: rwaitman Owned by: badagarla
Priority: major Milestone: heron-council-grove-update
Component: data-repository Keywords: public-web
Cc: Blocked By: 157
Blocking: 1696 Sensitive: no

Description

Several key observations are stored on this table for each encounter. The datetime of the observation should correspond with the CONTACT_DATE.

Key fields are:
BP_SYSTOLIC
BP_DIASTOLIC
TEMPERATURE
PULSE
WEIGHT
HEIGHT
RESPIRATIONS

LMP_DATE
LMP_OTHER_C map to zc_lmp_other
HEAD_CIRCUMFERENCE
BMI
BSA

Attachments (1)

enc_vitals_etc.sql (24.9 KB) - added by dconnolly 4 years ago.
achoudhary's script

Download all attachments as: .zip

Change History (21)

comment:1 Changed 4 years ago by dconnolly

  • Blocked By set to 157

(In #157) thinking about dependencies between tickets in the alpha milestone...

comment:2 Changed 4 years ago by rwaitman

BP_SYSTOLIC - systolic blood pressure
BP_DIASTOLIC - diastolic blood pressure
TEMPERATURE - body temperature
PULSE - pulse
WEIGHT - weight in kilograms
HEIGHT - height in centimeters
RESPIRATIONS - respirations per minute

LMP_DATE - last menstrual period
LMP_OTHER_C map to zc_lmp_other
HEAD_CIRCUMFERENCE - head circumference in centimeters
BMI - body mass index
BSA - body surface area

Call the concept id
PAT_ENC:PULSE

comment:3 Changed 4 years ago by achoudhary

  • Owner changed from achoudhary to dconnolly
  • Status changed from new to assigned

Here is the Code to build the concept Dimension, i2b2 table and load it into observation fact for the "vital Sign Measurement"

moved to attachment:enc_vitals_etc.sql

comment:4 Changed 4 years ago by dconnolly

  • Owner changed from dconnolly to achoudhary

comment:5 Changed 4 years ago by achoudhary

  • Owner changed from achoudhary to dconnolly

Changed 4 years ago by dconnolly

achoudhary's script

comment:6 Changed 4 years ago by dconnolly

We just discussed the way i2b2 requires C_METADATAXML to be populated in
order for the Set Value... query feature to work. Sure enough,
the client code checks for it specially and even checks for a Loinc element:

source:i2b2-webclient/webclient/js-i2b2/cells/CRC/CRC_view_QryTool.js#L112

	// For lab tests...
	var lvMetaDatas = i2b2.h.XPath(i2b2.CRC.view.QT.contextRecord.origData.xmlOrig, 'metadataxml/ValueMetadata[string-length(Loinc)>0]');
	if (lvMetaDatas.length > 0) {
		mil.push( { text: "Set Value...", onclick: { fn: op.ContextMenuRouter, obj: 'labvalues' }} );
	}

comment:7 Changed 4 years ago by dconnolly

The June 2010 LOINC Users' Guide says:

We have defined fields in the database for a number of data elements, e.g., typical units, sample normal ranges, but most of those fields are only partially populated.

(emphasis mine)

comment:8 Changed 4 years ago by rwaitman

  • Priority changed from major to minor

comment:9 Changed 4 years ago by rwaitman

  • Milestone changed from EpicAlphai2b2 to EpicBetai2b2
  • Owner changed from dconnolly to dzhu

Dongsheng will pick this up now and learn from Dan our ETL processes.

comment:10 Changed 4 years ago by rwaitman

  • Milestone changed from EpicBetai2b2 to heron-update
  • Sensitive unset

comment:11 Changed 4 years ago by dconnolly

  • Milestone changed from heron-update to HERONv.Next

comment:12 Changed 2 years ago by dconnolly

  • Milestone changed from HERONv.Next to heron-council-grove-update
  • Owner changed from dzhu to badagarla
  • Priority changed from minor to major
  • Type changed from task to enhancement

related to flowsheets and CAP (MedicalTerminologyMarketplace)

comment:13 Changed 2 years ago by ngraham

  • Blocking set to 1696

comment:14 Changed 2 years ago by badagarla

Created the transform and concept load code.
Load fails with cx_Oracle.DatabaseError: ORA-01722: invalid number
Cause might be instance number.

Will investigate tomorrow

comment:15 Changed 2 years ago by badagarla

The error from comment:14 as resolved when I converted encounter_ide to char.Now a different error:

2013-01-10 20:59:36.911000: id_db: <sid> on <server>
select case when count(*) > 0 then 1/0 else 1 end deid_age_over_90_pd from(
  select 
    pat.age_in_years_num 
  from 
    blueherondata.patient_dimension@deid pat
  where
    pat.age_in_years_num > 90
  )
Database Error: ORA-01476: divisor is equal to zero
ORA-02063: preceding line from DEID

Cursor_InternalExecute()

Was able to bypass the error by commenting out the test deid_age_over_90_pd in epic_i2b2_deid_verify.sql

Was able to run heron_load & load_all_concepts successfully, but some LMP facts were missing. This was due to mismatch in case of concept_cd.

comment:16 Changed 2 years ago by badagarla

capture query - done.changeset:b5e7a18f2355

comment:17 Changed 2 years ago by badagarla

  • Resolution set to fixed
  • Status changed from assigned to closed

comment:18 Changed 2 years ago by dconnolly

quick code review thoughts on [621e4e099a74]:

  • performance: I see union where I suspect union all is more appropriate
    • Is the implicit 'select distinct' actually required?
  • style: odd indentation in the .py changes

comment:19 Changed 2 years ago by dconnolly

comment:20 Changed 23 months ago by dconnolly

  • Keywords public-web added

ok by RW/NG

Note: See TracTickets for help on using tickets.