We hope you'll join us for our 4/23 webinar on using data tables to apply reference ranges and AE codes in OC4. For more information and to register, visit https://register.gotowebinar.com/register/2882170018956684555

First column only

Dear Forum Participants,

my project team has a problem when exporting data from OpenClinica (version 13.3). Only part of the information is extracted.
In concrete terms, this means that "Study Subject ID" and "Protocol ID" are always exported and then exactly one more column with content. All subsequent columns have only the heading, but are empty.

OpenClinica does NOT throw an exception or error and we have tried both Excel Export and Tab-delimited Export.

Does anyone have an idea what causes the behavior described above with only exporting one column containing content?

Thank you very much,
saalfelb

##################################

Details about our system:
OpenClinica Community Version: 3.13 - Changeset: 74f4df3481b6 2017-02-28 15:27 +0000
Server version: Apache Tomcat/7.0.68 (Ubuntu)
Server built: Jun 27 2016 18:13:17 UTC
Server number: 7.0.68.0
OS Name: Linux
OS Version: 4.4.0
Architecture: amd64
JVM Version: 1.8.0_151-8u151-b12-0ubuntu0.16.04.2-b12
JVM Vendor: Oracle Corporation
Tagged:

Comments

  • GerbenRienkGerbenRienk Posts: 815 ✭✭✭
    Hi Saalfelb,
    Three things come to mind, in no particular order:
    1 - did you set the country and language for java?
    2 - did you extract in ODM-XML-format to check for any special content of the data?
    3 - would it be possible for you to downgrade your JVM version to 1.7?
    Kind regards,
    Gerben Rienk
  • saalfelbsaalfelb Posts: 4
    Dear Gerben Rienk,
    thank you very much for your fast response.

    1. We did set Java Country and language to US/en.
    2. ODM-XML-format contains the required data. So we are going to extract with xslt.
    We are wondering why the rather complex ODM-XML contains everything, but the simple csv has a lack of data o.O

    Kind regards,
    saalfelb
  • toskriptoskrip Posts: 273 ✭✭✭
    Hi,

    good would be to check the ODM-XML metadata. The tabular csv export is basically the result of XSLT postprocessing of the XML based export. The postprocessing is likely to fail if the XML has unexpected structure. This would be the indication for the problem that you are seeing. I remember once I had such broken exports and the reason was that in the ODM-XML within ODM->Study->MetaDataVersion->Protocol one specific StudyEventRef was present there twice. This was caused by messed up OrderNumber of EventDefinition in database. This had to be corrected on DB lvl.

    If you cannot afford to do any correction on DB lvl. Then other workaround is to correct the metadata in exported XML and afterwards runs XSLT transformation that should then lead to the correct tabular export.

    Tomas
  • saalfelbsaalfelb Posts: 4
    edited April 23
    Dear Tomas,

    what a great hint! =D

    In fact, we seem to have a sequencing problem.
    One line is visible twice visible in ODM->Study->MetaDataVersion->Protocol :smile:


    It also has two separate definitions in StudyEventDef.
    Together they form the full Event we intended to define.

    You mentioned, you corrected it on DB level. Do you any further hints what you did in detail?

    Thanks

    ##############################################
    Excerpt from ODM-XML

  • toskriptoskrip Posts: 273 ✭✭✭
    yes, the issue is that you have two events with the same order number (SE_ABSCHLUSSUNTERSUCHUNG, SE_SYSTEMRCKGABE... they both have OrderNumber 10). This is something that I seen to happen when you are reordering events (there is somewhere a bug in OC). What you can do on DB is to select event definitions for your study by study OID to check their order number:

    SELECT s.unique_identifier, sed.study_event_definition_id, sed.name, sed.ordinal FROM study_event_definition sed LEFT JOIN study s ON sed.study_id = s.study_id WHERE s.oc_oid = 'S_DEFAULTS1' ORDER BY sed.ordinal;

    And then update event definitions one by one to give them new sequential order numbers where necessary (so that you do not have duplicates):

    UPDATE study_event_definition SET ordinal = integer WHERE study_event_definition_id = primary key;

    Make sure that you backup the database before you start doing changes directly on DB and properly document what you are doing and why.
  • saalfelbsaalfelb Posts: 4
    Thank you so much. It solved all our problems =D
Sign In or Register to comment.