Where Clause Builder

The Where Clause Builder is a specialized data filter that uses WHERE expressions to filter out and select observations meeting one or more specific defined criteria. A WHERE expression can be as simple as to use a single variable name. A WHERE expression can contain a function, or it can be a sequence of operands and operators that define one or more conditions for selecting observations. This report makes extensive use of JMP's Data Filter and you should refer to the Data Filter documentation for more information. The limitation of the Where Clause Builder, compared to the Subpopulation Builder, is that Where statements are only applied to a single domain.

The Where Clause Builder is surfaced from within a report when you click on one of the Additional Filters in the Options pane. Currently, the Where Clause Builder is used in Adverse Events Narrative and Risk Based Monitoring. The Where Filters for Adverse Events (AE) Protocol Deviations (DV), Inclusion/Exclusion Criteria Not Met (IE), Healthcare Encounters (HO), Disposition (DS), Findings (EG, LB, or VS) or Subject (DM or ADSL, depending on which is present) subsets these particular domains to records of particular interest.

The example below uses the Disposition filter in Risk Based Monitoring. Here, the Disposition WHERE statement is used to select the records (one per subject) that indicate the subjects' completion or discontinuation status.

Click

from the Disposition filter

The Where Clause Builder opens.

The DS domain is automatically selected as the domain. All of the columns in the domain are listed and are available for selecting.

Double-click on a column to select that column. In this example, we select DSCAT and click

The column list changes to a list of the different term categories contained in the selected column.

Select DISPOSITION EVENT and click . You can see that we have selected 906 observations which account for the 906 subjects in the Nicardipine study.

Click to add the filter to the report.

Compound WHERE expressions

You might find it useful to combine multiple simple WHERE expressions into a single, compound WHERE expression. Expressions can be joined either by using Boolean terms, such as AND and OR. Using AND will filter to and include those entities meeting the requirements of all of the specified filters. Using OR will filter to and include those entities meeting any of the specified filters.

Click either or once you have specified your first filter.

In the following example, we restrict the analysis to subjects who experience severe abdominal distension and anaemia.

To generate the compound WHERE expression, you must first generate a simple WHERE expression.

Click

from the Adverse Events filter

The Where Claussubppe Builder opens.

The AE domain is automatically selected as the domain. All of the columns in the domain are listed and are available for selecting.

Click on a column to select that column. In this example, we select AETERM and click

The column list changes to a list of the different term categories contained in the selected column.

Select Anaemia and click to generate the simple WHERE expression.

Click (circled above) to generate a complex WHERE expression.

To complete the second portion of the expression, where AETERM is Abdominal Distension:

Select AETERM and click .

Click . to add the WHERE expression is added to the filter in the report.

Note: You can add additional complexity to the filter anytime during this process by clicking on additional and/or buttons.