Extraction of lab results from CCD into CSV or other formats suitable for Business Intelligence and Analytics tools

[Kevin Pinto]

Greetings to all,

I work as a QlikView BI consultant and am looking to extract lab results from the CCD into a format that can be easily imported into QlikView and analyzed and graphed.

Unfortunately, the test result section of the CCD is non-optimal for easy extraction (see real example from HealthVault below).

I'm looking for a converter that would extract the test results into a more easily consumable form, e.g. CSV or at least some better structured XML. I did find one utility, but it is written in Excel / VB and doesn't work all that well.  I eventually want the XML below to look like:

Name, Result, Unit, Range, Flag, Substance, Date

Thyroid-stimulating hormone (TSH), 1.19, uiU/ml (micro international units per milliliter), , , , 2012-10-30

...

If a converter does not exist, I can write one, in Python or Java.  I need this converter to be scriptable, capable of cranking through thousands of CCDs and assembling the output into a format that could be inserted into a traditional RDBMS or into a high performance BI / analytics tool.

Any thoughts or suggestions?

Thanks and regards,

Kevin Pinto

<code code="30954-2" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC" displayName="Relevant diagnostics test and/or laboratory data" />

  <title>Results</title>

- <text>

+ <table>

- <tbody>

- <tr>

- <td>

  <content styleCode="Bold">Thyroid-stimulating hormone (TSH)</content>

  </td>

  </tr>

  </tbody>

  </table>

- <table>

- <thead>

- <tr>

  <th>Name</th>

  <th>Result</th>

  <th>Range</th>

  <th>Flag</th>

  <th>Substance</th>

  <th>Date</th>

  </tr>

  </thead>

- <tbody>

- <tr>

  <td>Thyroid-stimulating hormone (TSH)</td>

  <td>1.19 uiU/ml (micro international units per milliliter)</td>

  <td />

  <td />

  <td />

  <td>2012-10-30</td>

  </tr>

  </tbody>

  </table>

[David Whitten]

I think the CCD has always been a bit, shall we say, recalcitrant.
Can you use a CCR instead? It is much more tractable.

As to your need, especially if you end up writing code, I expect that if you did it in two steps, with a tool for each, you would help the CCD-CCR folks a lot more. This would give more tools in the programmer's tool box, that would help everyone more.

The two tools I'm thinking of are:

1) create a CCR from the CCD

2) create the Comma Separated Value file (CSV) from the CCR

What say you?

David
713-870-3834

--
You received this message because you are subscribed to the Google Groups "CCD-CCR-project" group.
To view this discussion on the web visit https://groups.google.com/d/msg/ccd-ccr-project/-/3GwDKdLWhbEJ.
To post to this group, send email to ccd-ccr...@googlegroups.com.
To unsubscribe from this group, send email to ccd-ccr-proje...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/ccd-ccr-project?hl=en.

[Nancy Anthracite]

If you want to do this with VistA, I suspect George has something that can
help you already. Or has this got nothing to do with VistA?

--
Nancy Anthracite

> > <code code="*30954-2*" codeSystem="*2.16.840.1.113883.6.1*"
> > codeSystemName ="*LOINC*" displayName="*Relevant diagnostics test and/or
> > laboratory data* " />

> >
> > <title>Results</title>
> >
> > - <text>
> > + <table>
> > - <tbody>
> > - <tr>
> > - <td>
> >

> > <content styleCode="*Bold*">Thyroid-stimulating hormone

[Kevin Pinto]

Thanks for the responses!

@Nancy:  My need doesn't have anything to do with VistA.  However, if I am going to write code, I am certainly open to writing it in a manner that can be leveraged for VistA.

@David:

My source can provide CCRs instead of CCDs.  I obtained a CCR of the same data set.  The XML is better structured but will still require transformation into a tabular structure.

I don't know that I feel confident in writing a CCD to CCR converter.  The scope is large and I am new to this domain.  I don't like to write code in isolation without understanding the business well.

However, I could likely write an extractor that extracts test results from both CCDs and CCRs for a little extra effort.  If I were to do so:

a)  What tools / technologies / languages would be of most use to this community?

b)  Can I have someone review a design before I start work on it?

Thanks,

Kevin Pinto

[George Lilly]

Back in '09, I developed a Mumps based approach for parsing CCDs for import:

https://groups.google.com/forum/?fromgroups=#!topic/hardhats/pPaGsAPdtJI

I'm looking at maybe using that now for the MU Stage 2 requirements to process incoming xml documents. I have a similar set of code that will parse the CCR to a similar Mumps array. 

Last year I looked at transforming CCDs and CCRs to RDF format and processing the result. You can see examples and "how I did it" here:

http://glilly.net/arc/

We already have a way to transform a CCR into a CCD. You can download it here:

https://trac.opensourcevista.net/browser/ccr2ccd-xslt

The tools we have been using to date are Mumps and XSLT for these kinds of transformations.

My current thinking is that a "simplified" RDF xml format is probably the most flexible an useful. I can imagine a tool chain that first converts CCRs, CCDs, and CCDAs into RDF (there is a generic tool for this already); then, an "rdf mapping" tool for each format that converts the resulting RDF into "normalized" RDF; then any number of tools which take the normalized RDF and converts it to what you actually want: Text format for a report, Smart format for use in a Smart Container, CSV format for external system usage.... or just use the normalized form for your applications.

Kevin, is RDF a convenient format for your purposes? If so, we can talk further here about the mapping process and what a "normalized RDF form" might look like.

gpl

--
You received this message because you are subscribed to the Google Groups "CCD-CCR-project" group.

To view this discussion on the web visit https://groups.google.com/d/msg/ccd-ccr-project/-/jTKqsOVRkWAJ.