CQL Cheat Sheet

Foundation of CQL

CQL is a Health Level 7 Standard for the expression of clinical knowledge.

Syntax

Declarations

Constructs expressed within CQL are packaged in containers called libraries. Each library allows a set of declarations to provide information about the library as well as to define constructs that will be available within the library.

Library syntax -The library declaration specifies both the name of the library and optional version

library AlphoraCommon version '1.0.0'

Using syntax - A CQL library can reference zero or more data models using declarations via the using syntax. These data models define the structures that can be used within retrieval expressions in the library.

using FHIR version '4.0.1'

Include syntax - A CQL library can reference zero or more other CQL libraries with the include declarations.

include FHIRCommon called FC

Parameter syntax - CQL library can define zero or more parameters using the parameter declaration. A parameter includes an alias name for a default value or data type that can be used throughout the code by that alias value.

parameter MeasurementPeriod default Interval[@2013-01-01, @2014-01-01)

parameter MeasurementPeriod Interval<DateTime>

Context syntax- The context declaration defines the scope of data available to statements within the language. When no context is specified in the library, and the model has not declared a default context, the default context is Unfiltered. By contrast, if the Unfiltered context is used, the results of any given retrieve will not be limited to a particular context.

context Patient, context Practitioner, context Unfiltered

Value Sets & Code Systems- Value Sets and Code Systems are declared before they are referenced within the CQL code.

valueset "Acute Pharyngitis": 'urn:oid:2.16.840.1.113883.3.464.1003.102.12.1011'

Retrieve syntax

The retrieve expression is the central construct for accessing clinical information within CQL. The retrieve in CQL has four main parts: first, the type part and second, the filter part, function name and function.

the type part : identifies the type of data that is to be retrieved

[Encounter]

the filter part : the retrieve expression allows the results to be filtered using terminology, including value sets, code systems, or by specifying a single code.

[Condition: "Acute Pharyngitis"\] where Acute Pharyngitis is the value set.

3)the function name: the expression is given a name for its identification and possible reuse later on.

define "Inpatient Encounters"

function : A function in CQL is a named expression that is allowed to take any number of arguments. A function can be invoked directly by name. A colon must end the quoted identifier.

define "Inpatient Encounters": 
[Encounter: class = "Inpatient Encounter"]

Alias functionality

A query construct often begins by introducing an alias for the primary source.

["Encounter": "Inpatient"] E

where E.period during "Measurement Period"

Full Query Syntax

The clauses described in the clauses section later must appear in the correct order in order to specify a valid CQL query. The general order of clauses is:

<primary-source> <alias>
  <with-or-without-clauses>
  <where-clause>
  <return-clause>
  <sort-clause>

Quotation Syntax

Strings have single quotes (including string representation of code values)

'John Doe', 'g/dl'
'male'

Identifiers that include spaces or other non-alphnumeric characters have double quotes

"Marital Status - Married" // A Concept declaration
"SNOMED CT" // A CodeSystem declaration
"Inpatient Encounters" // A defined expression

Bracket Syntax

Intervals use [] and ()

Interval[3,5) // An interval >= 3 and < 5
Interval(3,5) // An interval > 3 and < 5
Interval(3,5] // An interval > 3 and <= 5

Lists and Tuples use { }

{ 1, 2, 3 } union { 3, 4, 5 }

define "Info": 
  Tuple { Name: 'Patrick', DOB: @2014-01-01 }

Queries

Single-source queries

CQL provides single-source queries to allow for the retrieval of data from a single source. The query in the example below returns "Ambulatory/ED Visit" encounters performed where the patient also has a condition of "Acute Pharyngitis" that overlaps after the period of the encounter. It will only return Encounters.

define Encounter Ambulatory
  [Encounter: "Ambulatory/ED Visit"] E
    with [Condition: "Acute Pharyngitis"] P
      such that P.onset during A.period
        and P.abatement after end of A.period

Multi-source queries

CQL provides multi-source queries to allow for the simple expression of complex relationships between different sets of data. In Multi-source queries multiple objects are returned.

define "Encounters with Warfarin and Parenteral Therapies":
  from "Encounters" E,
    "Warfarin Therapy" W,
    "Parenteral Therapy" P
  where W.effectiveTime starts during E.period and P.effectiveTime starts during E.period

Expressions

Conditional expressions

CQL provides two flavors of conditional expressions, the if expression, and the case expression.

If expression: It allows one single condition to be selected

if Count(X) > 0 then X[1] else 0

Case expression : It allows multiple conditions to be tested.

case
  when X > Y then X
  when Y > X then Y
  else 0
end

Values

Simple values

Boolean

True/False

Integer

16, -28

Decimal

100.015

String

'pending' , 'active', 'complete'

Date

@2014-01-25

DateTime

@2014-01-25T14:30:14.559

Time

@T12:00

Clinical Values

Quantities

3 months
5 'mg'

Ratio

5 'mg' : 10 'mL'

Code

code "Blood pressure": '55284-4' from "LOINC" display 'Blood pressure'

Tuples

define "PatientExpression": 
  Patient { Name: 'Patrick', DOB: @2014-01-01 }

Missing Information

null

List Values

{ 1, 2, 3, 4, 5 }

Interval values

Interval[3, 5)

Query Clauses

Where clause where exists + where not exists + exists to filter retrieved data

define "Inpatient Encounters":
  ["Encounter": "Inpatient"] Encounter
  where Encounter.period during "Measurement Period"

define "Quantitative Laboratory Encounters ":
  [Encounter] E
    where exists (["Observation"] O)

define "Encounter Without Procedure ":
  [Encounter] E
    where not exists
    ( [Procedure] P
      where P.performed as dateTime during E.period 
    )

define "Had chest CT in past year":
  exists ("Chest CT procedure" P
    where FC.ToInterval(P.performed) ends 1 year or less before Today() 
  )

With/Without clause + such that : to define relationships with other data. When multiple with or without clauses appear in a single query, the result will only include elements that meet the “such that” conditions for all the relationship clauses.

define "Inpatient Encounters":
  [Encounter": "Inpatient"] Encounter
    with ["Observation": "Streptococcus Test"] Lab Test
      such that Observation.issued during Encounter.period

Return clause : determines the overall shape of the query result.

define "Inpatient Encounters":
  [Encounter: "Inpatient"] Encounter
    return Encounter.period

Sort clause: to order the results ascending or descending.

define "Performed Encounters":
  ["Encounter,Performed" : "Inpatient"]Encounter
    sort by start/end of period.

Let clause: to help introduce content that can be referenced within the scope of the query, they do not impact the type of the result unless referenced within a return clause.

define "Medication Ingredients":
  "Medications" M
    let ingredients: GetIngredients(M.rxNormCode)
    return ingredients

Aggregate clause: determines the overall result of the query.

define FactorialOfFive:
  ({ 1, 2, 3, 4, 5 }) Num
    aggregate Result starting 1: Result * Num

Basic Operators

Indexing

Indexes are 0 based, index 1, is index 0

define "FirstInpatientEncounter":
  return (Encounter [0])

Arithmetic Operators

CQL provides a complete set of arithmetic operations for expressing computational logic.

Addition + , Subtraction - , Multiply * , Divide /
Truncate () – round the value backwards
Round () - round the value frontwards
Floor () - round to the greatest integer less than a decimal
Ceiling () - round to the least integer greater than a decimal
Convert ()

convert 5000 'g' to 'kg'.

Count ()

Count ({1, 2, 3, 4, 5})

Sum()

Sum({1,2,3,4}) // returns the sum of values

Indexing

IndexOf({ 'a', 'b', 'c’ }, 'b') // returns 1

List computation tools

Operating on Lists

if a list contains a single element, the singleton from operator can be used to extract it.

singleton from { 1 }

to obtain the index of a value within the list

IndexOf({'a', 'b', 'c' }, 'b')

to obtain the number of elements in a list

Count({ 1, 2, 3, 4, 5 })

Membership in lists can be determined using the in operator and its inverse, contains

{ 1, 2, 3, 4, 5 } contains 4
4 in { 1, 2, 3, 4, 5 }

To test whether a list contains any elements

exists ( { 1, 2, 3, 4, 5 } )

The First and Last operators can be used to retrieve the first and last elements of a list.

First({ 1, 2, 3, 4, 5 })

Comparing Lists

includes, includes in, properly includes and properly included in are tools we can use to compare lists

{ 1, 2, 3 } includes { 1, 2 } // returns true

Computing Lists

To eliminate duplicates.

Distinct({ 1, 2, 3, 4, 4})

To combine lists (union eliminates duplicates).

{ 1, 2, 3} union { 3, 4, 5 }

To only return the elements that are in both lists.

{ 1, 2, 3} intersection { 3, 4, 5}

The flatten operation can flatten lists of lists.

flatten { { 1, 2, 3 }, { 3, 4, 5 } }

Aggregate Operators

This would return the number of encounters in the list

Count([Encounter])

This would return the sum of the values in the list i.e 15

Sum({ 1, 2, 3, 4, 5 })

Date Time Operators

Comparing Dates and Times - Using comparison operators on dates

@2014-01-31 < @2014-02-01

Extracting Date and Time Components - extracts only date/time/year.

date from @2014-01-25T14:30:14 // returns @2014-01-25
time from @2014-01-25T14:30:14 // returns @T14:30:14
year from @2014-01-25T14:30:14 // return 2014

Date Time Arithmetic -

where we are subtracting 1 year out of todays date to return the date 1 year back from now

Today() - 1 year

Computing Durations and Differences -

duration in months between @2014 - 01 - 31 and @2014-02-01

Current Time Date Operators

Now() // Current date and time
Today() // Current Date
TimeOfDay() // Current Time

Interval tools

CQL supports the representation of intervals, or ranges, of values of various types.

General Interval Operators - contains, start of, end of,point from , width of

point from Interval[3, 3]
width of Interval[3, 5]

Comparing Intervals - the comparison between two interval values using a complete set of operations. This includes same as, before, meets before, overlaps before among others.

X same as Y
Interval [3,5) // includes all numbers >= 3 and < 5
start of Interval[3, 5) // 3
width of Interval[3, 5) // 1
end of Interval[3, 5) // 4

Timing operators on Intervals

same year as

Date(2014) same year as Date(2014, 7, 11)

during

["Encounter": "Inpatient"]
  where Encounter.period during "Measurement Period"

before/after

Date(2014, 4) same month or before Date(2014, 7, 11)
Date(2014, 4) same month or after Date(2014, 7, 11)

within

starts within 3 days of start (2014, 7, 11)

overlaps

[Condition: "Other Female Reproductive Conditions"] C
  where Interval[C.onsetDate, C.abatementDate] overlaps "Measurement Period"

Computing Intervals- Using union, intersect, except to compute intervals

Interval[1, 3] union Interval[3, 6]

Date Time Intervals

Interval[Date(2014), Date(2015, 1, 1)]

Comparison Operators

Can be used on numbers, strings, integers, dates, decimals

Equality =
Inequality !=
Greater than >
Less than <
Greater than or equal >=
Less than or equal <=
Equivalent ~
Inequivalent !~

4 >= 2 and 4 <= 8

Logical Operators

and, is, in, as, or, not

AgeInYears() >= 18 and AgeInYears() < 24

define TestPrimitives:
  Patient P
    where P.gender.value = 'male'
      and P.active.value is true
      and P.maritalStatus in "Marital Status"

define "Former smoker observation":
  "Most recent smoking status observation" O
    where (O.value as CodeableConcept) ~ "Former Smoker"

define "Absence of Cervix":
  [Procedure: "Hysterectomy with No Residual Cervix"] NoCervixProcedure
    where FC.ToInterval(NoCervixProcedure.performed) ends on or before end of "Measurement Period" 
      and NoCervixProcedure.status = 'completed'

Nullological Operators

Null Test is used to test whether an expression is null

X is null 
X is not null

Coalesce operator is used to return the first non-null result among two or more expressions

Coalesce(X, Y, Z)

String Operators

Length Operator is used to determine the length of string

Length(X)

PositionOf Operator is used to determine the position of a string and will return the index of the string

PositionOf('cde', 'abcdefg')

Combine operator is used to combine a list of strings

Combine({ 'ab', 'cd', 'ef' })

Split Operator is used to split a list of strings

Split('completed;refused;pending', ';') // returns { 'completed', 'refused', 'pending' }

Caveats

Sort clauses don’t require usage of an alias. We don’t need to say E.sort by length of stay.

[Encounter: "Inpatient"] E
  return Tuple { id: E.identifier, lengthOfStay: duration in days of E.period }
  sort by lengthOfStay

Return clause default behavior is to return distinct data. Eg. if the query is to retrieve all blood pressure observations and if there are two blood pressure observations on the same date, only one observation will be returned. The all operator should be used to undo the default distinct behavior. Eg. If two encounters have the same value for lengthOfStay, that value will only appear once in the result unless the all keyword is used

[Encounter: "Inpatient"] E
  return all E.lengthOfStay

Equality and Equivalence

Note the use of the equivalent operator (~) rather than equality (=). For codes, equivalence tests only if the system and code are the same, but does not check the version or display elements
String Equality is case sensitive

('Abel' = 'abel') is false

String Equivalence ignores case, locale, and normalizes whitespace (meaning all whitespace characters are equivalent).

('Abel' = 'abel') is true

The Sum function works on lists of quantities or numbers not a list of structures like Encounters
Inclusive a.k.a closed boundaries are indicated with square brackets [ ], and exclusive a.k.a open boundaries are indicated with parentheses ( ).

Provide feedback

Saved searches

Use saved searches to filter your results more quickly