1. Creating a Study
  2. Defining Data Collection Objects
  3. Validation and Derivation Procedures
  4. Special Processing

Special Processing

Oracle Clinical provides a user interface (UI) for defining several types of algorithms for processing your data. You enter information in the UI as specified in each section below. When you invoke Procedure generation, the system creates PL/SQL code based on your definitions, and inserts the code in the appropriate place in the generated Procedure. Special processing types include:

Parent topic: Validation and Derivation Procedures

Correlation

By default, a given Procedure fetches data from all Questions at all visits for each Procedure Question Group you specify. You can use correlation to limit the data processed by inner Procedure Question Group Cursors to data that matches values in outer Procedure Question Group Cursors.

Correlation processing is driven by the Question Group in the outer loop of the two Question Groups (which is the one displayed further up in the Question Groups window) because the outer loop is processed before the inner loop, but you must define correlation in the definition of the inner loop (the Question Group displayed lower in the window). See Procedure Question Group Cursors and Defining Procedure Question Groups.

As the Procedure processes the data in the outer loop, it processes only data in the inner loop that satisfy the correlation condition(s) you define. The current record, or record currently being processed, is a Question response of the Question Group displayed higher in the window, fetched in the outer loop. The Question Group whose data is limited by correlation is the one displayed lower in the window, for which you define correlation conditions, and whose cursor becomes the inner loop of the Procedure.

You can specify the same Question Group as the correlating Question Group (outer loop) for any number of Question Groups displayed lower in the Question Groups window.

Note:

You can use the Create Placeholder setting in combination with correlation to keep track of missing data. See About the Question Groups Window, "Create Place Holder?".

You can use as many combinations of correlation types as you need; for example, correlate the same two Question Groups on both Event and Question in the Time Since Dose example.

You can define three types of correlation, as follows. In each example, Question Group A is displayed higher in the window (forming the outer loop) and Question Group B is displayed lower (forming the inner loop of the two).

For more information, see:

Parent topic: Special Processing

Correlation on Event

Use correlation on an event, or visit, to process data collected only for certain events relative to the Event Number of the current record (from Procedure Question Group A). Choose Previous, Actual, or Next Event.

For example, you commonly need to compare responses from different Question Groups only with other responses collected at the same visit. In that case, correlate on the Actual Event. Then, when Question Group B is defined as correlated with Question Group A on the Actual Event, the Procedure runs an expression that compares responses from the two Question Groups only when the response from Question Group B was collected at the same visit as the response from Question Group A. If correlation by Event was not specified, the Procedure would run the expression comparing Question Group A's response to Question Group B's response from every visit.

If you specify correlation on the Previous or Next Event, you specify it in the definition of Procedure Question Group B, but it is from the perspective of Question Group A. For example, if Question Group B is defined as correlating with Question Group A on Event (Next), the Procedure runs the expression only when the response from Question Group B was collected at the visit after the visit of the current response from Question Group A. In other words, if the response from Question Group A was collected during Visit 5, the Procedure runs the expression only for the response collected from Question Group B at Visit 6.

Note:

To compare responses to the same Question for several visits, use a lag check. See Lag Checks.

Note:

In pre-V3.1-style Procedures you can correlate only with the Actual Event.

Example: Correlation on Event

This example shows the same situation as in Example: Procedure with Multiple DCMs, except that the two Question Groups are correlated by Event. Therefore the Procedure processes the record from the Lab Tests Procedure Question Group only when it was collected at the same visit as the current Physical Exam record.

Retrieve Patient 1 information

Retrieve responses and key values for Visit 1 Physical Exam for Patient 1

Retrieve responses and key values for Visit 1 Lab Tests for Patient 1

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 1

(No expressions are executed because the visit number does not match.)

Retrieve responses and key values for Visit 2 Physical Exam for Patient 1

Retrieve responses and key values for Visit 1 Lab Tests for Patient 1

(No expressions are executed because the visit number does not match.)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 1

For each detail, execute expression(s)

Retrieve Patient 2 information

Retrieve responses and key values for Visit 1 Physical Exam for Patient 2

Retrieve responses and key values for Visit 1 Lab Tests for Patient 2

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 2

(No expressions are executed because the visit number does not match.)

Retrieve responses and key values for Visit 2 Physical Exam for Patient 2

Retrieve responses and key values for Visit 1 Lab Tests for Patient 2

(No expressions are executed because the visit number does not match.)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 2

For each detail, execute expression(s)

Parent topic: Correlation

Correlation by Qualifying Value

Use correlation by qualifying value to compare responses to Questions that belong to DCMs with Qualifying Questions, only when the value of the DCM Qualifying Question is the same. The actual qualifying value is not specified, so the system will run the expression over the specified responses whenever their RDCMs' qualifying values are the same.

For example, you might have two DCMs with a qualifying value of either Left or Right, one DCM to collect general information about either the left eye or the right eye, and the other to collect information about abnormalities in one eye or the other. Correlation by qualifying value ensures that left eye general information at each visit is compared only with left eye abnormality information, and right eye general information is compared only with right eye abnormality information.

For information on DCMs with qualifying values, see Configuring Qualifying Questions.

Note:

If you want to run the expression only when the RDCM has a particular qualifying value, you can use the Where Clause Extension to specify the value in the DCM key variable qualifying_value (see DCM Key Variables in Standard Variables).

Example: Correlation by Qualifying Value

This example shows a similar situation to Example: Procedure with Multiple DCMs, except that both Question Groups belong to a DCM with a qualifying value, and are correlated by DCM qualifying value. Therefore the Procedure processes the record from the Lab Tests Procedure Question Group only when its DCM qualifying value is the same as the current Physical Exam record's DCM qualifying value.

Retrieve Patient 1 information

Retrieve responses and key values for V1 Physical Exam for Pat. 1 (DCMQV=Y)

Retrieve responses and key values for V1 Lab Tests for Pat. 1 (DCMQV=Y)

For each detail, execute expression(s)

Retrieve responses and key values for V1 Lab Tests for Pat. 1 (DCMQV=N)

(No expressions are executed because qualifying value does not match.)

Retrieve responses and key values for V2 Lab Tests for Pat. 1 (DCMQV=Y)

For each detail, execute expression(s)

Retrieve responses and key values for V2 Lab Tests for Pat. 1 (DCMQV=N)

(No expressions are executed because qualifying value does not match.)

Retrieve responses and key values for V2 Physical Exam for Pat. 1 (DCMQV=N)

Retrieve responses and key values for V1 Lab Tests for Pat. 1(DCMQV=Y)

(No expressions are executed because qualifying value does not match.)

Retrieve responses and key values for V1 Lab Tests for Pat. 1 (DCMQV=N)

For each detail, execute expression(s)

Retrieve responses and key values for V2 Lab Tests for Pat. 1(DCMQV=Y)

(No expressions are executed because qualifying value does not match.)

Retrieve responses and key values for V2 Lab Tests for Pat. 1 (DCMQV=N)

For each detail, execute expression(s)

Retrieve Patient 2 information--same processing as for Pat. 1 above

Parent topic: Correlation

Correlation by Question Value

When the Procedure compares values from paired sets of Question responses (a Question response common to both the current Question Group and the correlated Question Group), use correlating Questions. If two Question Groups collect data for a common series of nominal times (such as Time Since Dose), you can use the response value of the Time Since Dose Question to allow comparison testing with other Question responses collected at the same nominal time in the correlated Question Group.

Example: Correlation by Question Value

The processing of Question Groups correlated on Question Value looks the same as in Correlation by Qualifying Value. The difference is that the system compares a Question response instead of the DCM qualifying value before running the detail.

Parent topic: Correlation

Restrictions

The following restrictions apply to correlation:

  • You can correlate a Question Group only to Question Groups listed above it in the Procedure Question Groups window.

  • Correlation and aggregation cannot coexist in a single Procedure.

Parent topic: Correlation

Setting Up Correlation

To use correlation, do the following:

  1. In the Procedure Question Groups window, in the Correlate With Alias field, enter the alias of the Question Group with which you want to correlate the highlighted Question Group. The alias must be for a Question Group listed higher in the window than the highlighted Question Group.
  2. Enter a value for one or more of the following types of correlation:

    • Event. From the list of values, choose Previous, Actual, or Next. The system will run an expression on a Question response only against the correlated Question Group's response(s) that were collected at the previous, same, or next event, compared to the event of the current Question response.

    • Qualifying Question. Applicable only for Question Groups that belong to DCMs with qualifying values. If selected, the system will run an expression that compares Question responses from Question Groups correlated on Qualifying Value only when the value of both Question Groups' DCM's Qualifying Question is the same.

    • Correlating Question. Click the Correlating Questions button and enter the name of a Question from each of the correlated Question Groups (use the list of values). You can enter one or more pairs of Questions, one from each Question Group. The system will run the Procedure only when the values of each pair of Question responses are the same.

Parent topic: Correlation

Aggregate Functions

Aggregate functions compute an aggregate value for response values for a particular Question for a patient. Aggregate functions available are:

  • Maximum. The highest value.

  • Minimum. The lowest value.

  • Sum. A running total.

  • Average. The average of all specified responses.

  • Count. The number of specified responses collected.

Oracle Clinical creates a system variable for each function you specify and automatically populates the variable to hold the information generated by the function. By default, the system includes all values for the same Question for the same patient, but you can use a Where Clause Extension to filter the responses included in the aggregate function by the value of any of the keys available in that field (see Where Clause Extension).

Oracle Clinical aggregate functions are based on standard Oracle aggregate functions. Except for the Count function, none of them include null values in their calculations.

The system fetches one row per patient for an aggregate Question Group for all visits. You cannot use an aggregate function, for example, to compute an average of repeated Question values at a single visit (you can use an aggregate lag function for that purpose; see Aggregate Lag Functions).

You define a Question Group as aggregate by selecting its Aggregate? box, then clicking the Questions button, selecting one or more Questions, and specifying the aggregate function for each selected Question.

The system displays aggregate Question Groups before all other Question Groups, regardless of the order in which you define them. It fetches their values before other Question Groups' values, and executes details with aggregate functions before other details in a Procedure.

For more information, see:

Parent topic: Special Processing

Variable Names

The naming convention for the system variables is: alias.question_name[occurrence#]$suffix. The suffixes are: mn, mx, sm, av, and ct for the five functions available. The occurrence number is present only when there is more than one occurrence of the Question in the DCM Question Group.

Parent topic: Aggregate Functions

Restrictions

The following restrictions apply to aggregate functions:

  • A single Procedure cannot include both aggregate functions and correlation.

  • Qualifying expressions cannot be applied to an Aggregation Question Group (a Question Group with its Aggregate? box selected).

  • All Questions in an Aggregation Question Group must use at least one aggregate function.

  • Questions that use aggregate functions cannot use aggregate lag functions (see Aggregate Lag Functions).

Parent topic: Aggregate Functions

Setting up Aggregate Functions

To use aggregate functions, you must:

  1. In the Procedure Question Group window, select the Aggregate? box for the Question Group that contains the Question(s) on whose responses you want to run an aggregate function.

  2. In the Questions window for that Question Group, for each Question on whose responses you want to run an aggregate function, in the Aggregate Functions field enter each aggregate function you want to have performed. Use the list of values or enter more than one value by entering a value followed by a comma, followed by another value, and so on.

  3. From the main Procedures window, click the Details button. The Details window opens.

  4. Click the Variables button. The Variables window opens.

  5. In a new row in the Variables column, press F9 to open the System Variables window.

  6. Select all the aggregate variable(s) you need to use in the Procedure and click OK.

  7. Select Report? for each variable whose value you want to be able to see in the Discrepancy Database and in DCFs.

You can use the variables in detail expressions and custom code by pressing F9 from the Expression or Custom Code field and selecting them from the list of values.

Parent topic: Aggregate Functions

Aggregate Lag Functions

To compare responses to the same Question for the same patient over a set number of visits, and flag those that are inconsistent, use an aggregate lag function.

Aggregate lag functions operate over the last n responses to a particular Question collected for a particular patient. You set the Lag Depth n to the number of immediate past responses you want to include, and select the aggregate function you want to calculate for those responses.

For example, to obtain the average of a patient's heart rates at his last three visits, mark the Procedure Question HEART_RATE with a Lag Depth of 3 and enter AVG into the Aggregate Lag Function field for that Procedure Question.

In aggregate lag functions you have two choices that are unavailable in aggregate functions:

  • You can choose to include the current value in the aggregate lag function or not. If you include the current value, the system calculates the aggregate lag function on the current value plus the most recent n values.

  • You can choose to include null values in the aggregate lag function calculation or not.

    • If you do not include null values, the system calculates the aggregate lag function on the n most recent non-null values.

    • If you do include null values, the system calculates the aggregate lag function on the n most recent values, whether the values are null or non-null.

You can use aggregate lag functions on any numeric Question in any Question Group that does not have its Aggregate? box selected. The Aggregate? box applies only to aggregate functions (see Aggregate Functions).

Oracle Clinical creates a system variable for each function you specify and automatically populates the variable to hold the information generated by the function, as follows:

  • Maximum. The highest of the last n values entered as a response to the Question for the patient.

  • Minimum. The lowest of the last n values entered as a response to the Question for the patient.

  • Sum. A total of the last n responses to the Question for the patient.

  • Average. The average of the last n responses to the Question for the patient.

  • Count. The number of the last n responses collected for the Question for the patient. In aggregate lag functions, Count will equal n except at the beginning of the study, when fewer than n responses have been collected for a Question for a patient.

For more information, see:

Parent topic: Special Processing

Variable Names

Oracle Clinical creates two types of system variables for aggregate lag functions:

  • Lag Variable. The system creates a lag variable to hold each value needed for the Lag Depth you specify. If you specify a Lag Depth of 3, the system creates three lag variables to hold the values of the three most recent Question responses. These variables follow the naming convention alias.question_name[occurrence#]$Ln. The occurrence number is present only when there is more than one occurrence of the Question in the DCM Question Group.

  • Aggregate Variable. The system creates a variable for each aggregate function you specify for the Question. The naming convention for the system variables is the same as for the aggregate function: alias.question_name[occurrence#]$suffix. The suffixes are: mn, mx, sm, av, and ct for the five functions available. The occurrence number is present only when there is more than one occurrence of the Question in the DCM Question Group.

Parent topic: Aggregate Lag Functions

Restrictions on Aggregate Lag Functions

A Question Group cannot support both aggregate functions and aggregate lag functions. If you want to use an aggregate lag function, do not define the Question Group as an aggregate one (do not select its Aggregate? box).

Parent topic: Aggregate Lag Functions

Using Aggregate Lag Functions

Do not select the Aggregate? box for the Question Group. That flag is for the aggregate function. See Aggregate Functions.

To use aggregate lag functions, you must:

  1. In the Questions window, for each Question on whose responses you want to run an aggregate function, enter values in the following fields:

    • Lag Depth. Enter the number of responses whose values you want to include in the lag function's calculation. For example, if you enter "5" the system will include the last five responses collected, within the limits you specify in the fields Include Null in Lag? and Include Current Event in Aggregate Lag?.

    • Include Null in Lag? Select this box if you want to include null values in the lag function's calculations. The default value is deselected.

    • Aggregate Lag Function. Enter each aggregate function you want to use. Use the list of values or enter more than one value by entering a value followed by a comma, followed by another value, and so on.

    • Include Current Event in Aggregate Lag? If selected, the aggregate lag function includes the value of the current event in its calculations.

  2. In the Details window, click the Variables button.
  3. Press F9 to open the System Variables window.
  4. Select the aggregate lag variables and click OK.
  5. Select Report? for each variable whose value you want to be able to see in the Discrepancy Database and in DCFs.

You can use the variables in expressions and custom code.

Parent topic: Aggregate Lag Functions

Lag Checks

In a lag check, you write the logic to compare the most recent n response values to the same Question for the same patient, where n is the Lag Depth you specify. Oracle Clinical creates a system variable for each of the last n responses. You can use these variables in writing expressions and custom code.

For example, if you want a Procedure to check whether a value is increasing over time, you can compare the current value with the single most recent previous value of a Question by using a Lag Depth of 1.

You can choose to include null values in the lag check or not.

In the code you write, you can include the current value in any way you choose.

For more information, see:

Parent topic: Special Processing

Variable Names

The naming convention for the system variables is: alias.question_nameoccurrence#$Ln.

The occurrence number is present only when there is more than one occurrence of the Question in the DCM Question Group.

Parent topic: Lag Checks

Setting Up Lag Checks

Do not select the Aggregate? box for the Question Group that contains a Question on whose responses you want to run a lag check.

To create a lag check, you must:

  1. In the Questions window, for each Question on whose responses you want to run a lag check, in the Lag Depth field enter the number of responses whose values you want to include in the lag check. For example, if you enter "5" the system will create system variables for the values of the last five responses to the Question for the patient.
  2. In the Details window, click the Variables button.
  3. Click F9 to open the System Variables window.
  4. Select the lag variables and click OK.
  5. Use the variables in custom code.

Note:

Lags are not reset to null after each visit, so you need to design Procedures to take that into account when testing the first n Questions in repeating Question Groups.

Parent topic: Lag Checks

Lag Check Examples

If you have a Procedure Question Group called MEDS, alias M, and in it a Question named DOSE, when you specify a Lag Depth of 3, the system creates three lag variables: M$dose$L1, M$dose$L2, and M$dose$L3. In addition, you still have access to the current value of the Question M.DOSE. You could create a discrepancy if the dose did not increase from one visit/repeat combination to the next by making this statement in the test detail: M$DOSE$L1>= M.DOSE. This evaluates to TRUE if the value from the previous visit/repeat combination is greater than or equal to the current visit/repeat, and therefore creates a discrepancy.

The first lag ($L1) of the first repeat of a visit is null. In general, the nth lag of the first n values seen by the system are null.

Other lag check examples:

Populating Lag Variables

Suppose the data for a non-repeating Question Group on a given Question, DOSE, is as follows (note that here the sort order of the Question Group is ascending):

Visit D.Dose

1

10

2

20

3

30

4

40

5

100

As the data is fetched in the Procedure, the variables are populated as follows:

Visit D.DOSE D$DOSE$L1 D$DOSE$L2 D$DOSE$L3

1

10

null

null

null

2

20

10

null

null

3

30

20

10

null

4

40

30

20

10

5

100

40

30

20

Lag Values for Data from a Repeating Question Group

The Lag Depth applies to previous repeats in the same visit and then previous visits, in descending order. For example, for the Question Group alias AE with three repeats, Question AMOUNT, the lag variables would be populated as follows with successive values 10, 20, 50, 40, and 60:

Visit Repeat AE.AMOUNT AE$AMOUNT$L1 AE$AMOUNT$L2

1

1

10

null

null

1

2

20

10

null

1

3

50

20

10

2

1

40

50

20

2

2

60

40

50

Including Null in Lag

When Include Null in Lag? is selected, variables are populated with the null value when no value is collected:

Visit AE.AMOUNT AE$AMOUNT$L1 AE$AMOUNT$L2

1

10

null

null

2

20

10

null

3

null

20

10

4

40

null

20

Not Including Null in Lag

When Include Null in Lag? is deselected, the system populates the lag value with the previous non-null value:

Visit AE.AMOUNT AE$AMOUNT$L1 AE$AMOUNT$L2

1

10

null

null

2

20

10

null

3

null

20

10

4

40

20

10

Qualifying Expressions and Lags

Qualifying expressions and lags interact in that lag variables are allowed in qualifying expressions; but the expression is evaluated before the lag is calculated. For example, if the qualifying expression is Q1=10, and the data is originally:

Qualifying Expression Value 1 Value 2 Value 3 Value 4

Q1

10

20

10

10

Q2

a

b

c

d

then the values seen in the test detail and qualifying expressions would be:

Pass Test Details Q1 Value Q2 Value

1st Pass

Q1=10, Q2=a

Q1$L1 is null

Q2$L1 is null

2nd Pass

Q1=10, Q2=c

Q1$L1 = 10 (not 20)

Q2$L1 = a (not b)

3rd Pass

Q1=10, Q2=d

Q1$L1 = 10

Q2$L1 = c

Parent topic: Lag Checks

Where Clause Extension

For each Procedure Question Group, you can write a PL/SQL Where clause statement (up to 200 characters) specifying DCM key value(s) and/or a Response Repeat Sequence Number as criteria to filter the records retrieved for processing by the Procedure. See DCM Key Variables in Standard Variables for a list of DCM keys. To filter on other criteria, use the Qualifying Expression field (see Qualifying Expression).

The Where clause extension is very efficient because the filter occurs before each Question Group fetch.

Note:

Do not use the Where clause extension in Derivation Procedures.

To define a Where clause extension:

  1. In the Procedure Definitions window, highlight the Procedure and click the Question Groups button. The Question Groups window opens.
  2. In the Where Clause Extension field, enter the Where clause.

    • Do not include the word "where." The system supplies it in the generated code.

    • Use the list of values to select variables to use as criteria in the statement.

  3. Save.

Parent topic: Special Processing

Qualifying Expression

For each Procedure Question Group, you can write a PL/SQL statement (up to 2,000 characters) specifying variable values as criteria to filter the records processed by the Procedure.

The qualifying expression approach is less efficient than the Where clause because the filter occurs after data has been fetched. However, because the cursor evaluates each Question Group after the fetch, you can filter on any criteria.

In V3.1-style Procedures, the Where clause extension is more efficient because it filters before the fetch, but the criteria are limited to DCM key fields.

Note:

Do not use the Qualifying Expression field in Derivation Procedures.

To define a qualifying expression:

  1. In the Procedure Definitions window, highlight the Procedure and click the Question Groups button. The Question Groups window opens.
  2. In the Qualifying Expression field for the Question Group, enter the expression. Use the list of values to select Question variables to use as criteria in the statement. You can use other variables as well; for lists of all the different types of variables, see Procedure Variables.

    Note:

    The list of values includes all the Questions defined for the Procedure Question Group, even if they have not been selected for use in this Procedure. If you select a Question here, you must also define it as part of the Procedure by selecting Questions under Question Groups.

  3. Save.

Parent topic: Special Processing

Custom Code

You can add your own PL/SQL code at certain points in the generated PL/SQL Procedure code. An extended list of values for variables is available by pressing F9. From the list of values, discrete value group information is available. You can enter up to 4000 characters.

For example, use pre-Details custom code to perform procedural logic prior to the Detail, accumulate information for previously retrieved DCM or patient records, or retrieve information not available in the generated cursors. Or, in post-Details code you can make calculations in preparation for the next cursor fetch, or maintain indexes for array variables. You must define variables before referencing them in code (see Defining User Variables and Using Variables in V3.1-Style Procedures).

Oracle Clinical places your custom code in the generated PL/SQL code at the location you specify in the Custom Code Location menu. See Sample Procedure Code, with Custom Code Locations for the exact location of the custom code types.

In pre-V3.1-style Procedures your custom code choices are limited to:

  • PRE-DETAILS, immediately before the Details block; previously called Pre-Code

  • POST-DETAILS, immediately after the Details block; previously called Post-Code

In V3.1-style Procedures your choices include:

  • PRE-DETAILS, immediately before the Details block; previously called Pre-Code

  • POST-DETAILS, immediately after the Details block; previously called Post-Code

  • DECLARATION — at the main Procedure declaration section

  • MAIN-BEGIN — at the beginning of the main Procedure

  • POST-PATIENT — immediately after the patient fetch

  • POST-QG — after Question Group fetch and any placeholder logic

  • MAIN-END — at the end of the main Procedure

Note:

In 3.1-style Procedures, you must explicitly allow for the possibility of pointing to either test or production database tables. See Converting to the V3.1 Style.

Parent topic: Special Processing