Results, SQL and report

Running

Run query executes the query on the cohort’s database. While it computes, the button becomes Stop: an over-broad query can be interrupted without waiting.

The header of the results panel then gives the count — the number of rows at the chosen level —, how long it took, and a CSV button that downloads the rows shown.

When a run fails, the panel says so and gives the database’s message. The most frequent case — No query could be built from these criteria — points to a Concept criterion with no concept selected, or a database mapping that does not declare the table for the chosen level.

The result tabs

The right-hand panel has up to six tabs.

• Results — the cohort’s rows: the id at the chosen level, age at admission, start and end dates.
• Attrition — the count after each criterion, detailed below.
• Patients — reviewing the members, chart by chart.
• Tables — every table of the database, filtered on the cohort.
• SQL output — the raw rows of a hand-written query.
• Schema — the database explorer, unfiltered, to find a table or column name while writing SQL.

Patients, Tables and SQL output

The Patients tab opens a board of the cohort’s own: the same tabs and widgets as the Patient data page, listing the patients of the last run — there is no need to materialize the cohort. On first opening the board is empty: Add widget lays it out, and it stays attached to the cohort.

The Tables tab lists every table of the database, filtered on the finest id it carries — the cohort’s unit stays, its hospital stays or its patients —, as a derivation would filter it. Clicking a table counts it and shows its first rows; Count all counts them all. A table without an id, such as a care-unit reference, stays Not filtered. It is the quickest way to see what the cohort brings with it: how many measurements, how many prescriptions.

The SQL output tab shows what a query written in the SQL tab returns, whether or not it lists the cohort’s members — SELECT * FROM measurement LIMIT 10 to glance at a table. When a query does not return the expected id column, Linkr opens this tab directly rather than showing only an error.

Reading attrition

The Attrition tab gives what every publication asks for: how many patients remain after each criterion, applied in the tree’s order. Each top-level criterion is a step; a group counts as a single step, under its name — hence the value of naming your groups.

Two presentations, chosen at the top right:

It is the most instructive reading on the page. A criterion that drops the count from 4,000 to 40 is almost always a badly written criterion — a concept absent from this database, an unexpected unit of measure — rather than a genuinely rare population.

The cohort’s SQL

The Criteria / SQL toggle in the toolbar shows the query your criteria produce. Its first use is to understand, and to check. It can also be edited.

1-- One row per patient: its patient_id
2SELECT DISTINCT
3  p.subject_id AS patient_id
4FROM hosp.patients p
5JOIN hosp.admissions a ON a.subject_id = p.subject_id
6WHERE p.anchor_age >= 18
7  AND EXISTS (
8    SELECT 1 FROM hosp.diagnoses_icd d
9    WHERE d.subject_id = p.subject_id
10      AND d.icd_code IN ('A419', 'R6521'))
11  AND p.dod IS NULL

The line at the top recalls the query’s contract: it must return a patient_id column — one row per patient (visit_id at hospitalization level, visit_detail_id at unit-stay level).

Portable or on the source tables

The format menu offers two writings of the same query:

When the criteria cannot be written on the database’s own tables, only the portable format is offered.

Editing, saving, resetting

Typing in the editor creates an unsaved edit, flagged by an orange dot — in the editor’s bar and on the SQL tab. Save (or Cmd+S) keeps it; Cancel goes back to the last saved text. A saved query carries the Modified badge, and an amber dot on the SQL tab recalls it even while you are on the criteria. Reset goes back to the SQL the criteria generate, Copy puts the query on the clipboard.

A saved query is shown as it was written: the format can no longer be switched until you reset it.

Run query always runs what the editor shows, saved or not. A result obtained from an unsaved edit carries the Unsaved SQL badge: saving keeps that definition, cancelling drops the result. Report and Materialize, for their part, read the saved definition — they stay greyed out while an edit is pending, and hovering says why.

The cohort report

Report produces a self-contained summary of the cohort, computed fresh from the database: counts, inclusion flowchart, criteria applied, concepts used and their coverage, characteristics — age at index date, sex, month of inclusion —, data available per event table, stays per care unit, methodology and data source. The dialog’s preview is the exported file itself: what you read is what is sent.

-- One row per patient: its patient_id
SELECT DISTINCT
  linkr_patient.patient_id
FROM linkr_patient…

Three settings, in the left-hand column:

• Format — HTML: a single file that opens in any browser; PDF: goes through the browser’s print dialog, where you choose “Save as PDF”; Word: a .docx to rework.
• Small-cell threshold — any count between 1 and this threshold shows as <N, with no percentage and no bar, so that no small group of patients can be singled out. 11 is the default, the usual rule for health data. A new threshold re-runs the report’s thirty-odd queries: it applies with Recompute, or Enter.
• Include the SQL query in the methodology — the exact query, so a reviewer can re-run it.

Export downloads the file in the chosen format.

Going further

• Building a cohort — the level, the criteria and how they combine.
• Cohorts — what a cohort is, and what it feeds.
• Patient data — reviewing the members’ charts, and laying out a board.
• Schemas — the mapping the portable SQL relies on.
• Derived sub-databases — drawing a new database from a cohort’s members.