TPLm Syntax Overview

TPLm (Table Producing Language for Malloy) uses a declarative syntax to define cross-tabulated tables.

Program Structure

A TPLm statement defines a table layout with optional filters:

TABLE [FROM source] [WHERE condition] ROWS <row-axis> [COLS <column-axis>];

Dimensions (categorical groupings) and measures (numeric aggregations) are defined in your Malloy source using .extend(), then referenced in TPL statements.

TIP

See the Data Model page for dimensions and measures available in the playground, and Quick Start for how to define your own.

Table Structure

TABLE [FROM source] [WHERE condition] ROWS <row-axis> [COLS <column-axis>];

Components

• FROM (optional): Malloy source name to query (see Data Sources)
• WHERE (optional): Filter condition (passed through to Malloy)
• ROWS: Row axis definition (required)
• COLS: Column axis definition (optional)

Examples

Simple Table

Create a basic table with one dimension:

Simple Table

TABLE ROWS occupation * income.sum;

Dataset:samplesoccupation, education, gender, income

Crosstab

Cross rows with columns to create a pivot table:

Basic Crosstab

TABLE ROWS occupation * income.sum COLS education;

Dataset:samplesoccupation, education, gender, income

With Totals

Add row and column totals using ALL:

With Totals

TABLE
  ROWS (occupation | ALL) * income.sum
  COLS education | ALL;

Dataset:samplesoccupation, education, gender, income

Quick Reference

Syntax	Meaning	Example
* or BY	Cross (nesting)	state * city or state BY city
| or THEN	Concatenate	state | gender or state THEN gender
ALL	Total row/column	(state | ALL)
[-N]	Top N (descending)	state[-10]
[N]	Top N (ascending)	state[10]
@field	Order by field	state[-10@births.sum]
:format	Number format	income.sum:currency
"label"	Custom label	state "US State"
ACROSS	Percentage	income.sum ACROSS COLS

Core Operators

Nesting (* or BY)

The * operator creates hierarchical relationships:

Nested Dimensions

TABLE ROWS occupation * gender * income.sum;

Dataset:samplesoccupation, education, gender, income

Each occupation contains a breakdown by gender.

Alternative Syntax

You can use BY instead of * for a more semantic feel: occupation BY gender BY income.sum

Concatenation (| or THEN)

The | operator creates side-by-side sections:

Concatenated Sections

TABLE ROWS (occupation | gender) * income.sum;

Dataset:samplesoccupation, education, gender, income

Occupation section appears first, followed by gender section.

Alternative Syntax

You can use THEN instead of | for a more semantic feel: occupation THEN gender

Using Alternative Syntax

The BY and THEN keywords can make complex queries more readable:

-- Traditional syntax
TABLE ROWS occupation * gender * income.sum COLS education | ALL;

-- Alternative syntax - same result
TABLE ROWS occupation BY gender BY income.sum COLS education THEN ALL;

Both operators work everywhere, including in aggregation lists: income.(sum THEN mean) is equivalent to income.(sum | mean).

Aggregations

Single Aggregation

Bind a measure to an aggregation method:

TABLE ROWS occupation * income.sum;
TABLE ROWS occupation * income.mean;
TABLE ROWS occupation * income.count;

Multiple Aggregations

Show multiple statistics side by side:

Multiple Aggregates

TABLE ROWS occupation * income.(sum | mean);

Dataset:samplesoccupation, education, gender, income

Available Aggregations

Basic:

• sum - Sum of values
• mean / avg - Average
• count / n - Row count
• min / max - Minimum / Maximum
• stdev - Standard deviation

Percentiles:

• median / p50 - Median (50th percentile)
• p25, p75 - Quartiles
• p90, p95, p99 - Higher percentiles (useful for latency, response times)

Mean vs Median vs P95

TABLE ROWS occupation * income.(mean | p50 | p95);

Dataset:samplesoccupation, education, gender, income

Limits and Ordering

Alphabetic Limits

state[10]     -- First 10 alphabetically (A-J)
state[-10]    -- Last 10 alphabetically (Q-Z)

Value-Based Ordering

Top N by Value

TABLE ROWS occupation[-5@income.sum] * income.sum;

Dataset:samplesoccupation, education, gender, income

Orders occupations by income and shows top 5.

Order Without Limit

Show all values in a specific order:

occupation@income.sum          -- All, ordered by income (desc)
occupation ASC@income.sum      -- All, ordered by income (asc)

Totals

Row Totals

Row Total

TABLE ROWS (occupation | ALL 'Total') * income.sum;

Dataset:samplesoccupation, education, gender, income

Column Totals

Column Total

TABLE ROWS occupation * income.sum COLS education | ALL 'Total';

Dataset:samplesoccupation, education, gender, income

Subtotals

Subtotals

TABLE ROWS occupation * (gender | ALL 'Both') * income.sum;

Dataset:samplesoccupation, education, gender, income

Percentages

Row Percentages

Each row sums to 100%:

Row Percentages

TABLE ROWS occupation * (income.sum ACROSS COLS) COLS education;

Dataset:samplesoccupation, education, gender, income

Column Percentages

Each column sums to 100%:

TABLE ROWS occupation * (income.sum ACROSS ROWS) COLS education;

Cell Percentages

Each cell as percentage of grand total:

TABLE ROWS occupation * (income.sum ACROSS) COLS education;

Formatting

Built-in Formats

income.sum:currency    -- $1,234.56
income.sum:integer     -- 1,235
income.sum:decimal.2   -- 1234.57
income.sum:percent     -- 45.6%
income.sum:comma.0     -- 1,235

Currency Format

TABLE ROWS occupation * income.sum:currency COLS education;

Dataset:samplesoccupation, education, gender, income

Custom Formats

Use # as a placeholder for the number, with .N to specify decimal places:

income.sum:'$ #.2'       -- $ 1,234.57
income.sum:'€ #.2'       -- € 1,234.57
income.sum:'#.0 units'   -- 1,235 units
income.sum:'#.1%'        -- 45.7%
income.sum:'$ #.2 M'     -- $ 1.23 M (for millions)

Custom Format

TABLE ROWS occupation * income.sum:'$ #.2' COLS education;

Dataset:samplesoccupation, education, gender, income

Labels

Add custom labels to any element:

Custom Labels

TABLE ROWS occupation 'Job Type' * income.sum 'Total Income' COLS education 'Education Level';

Dataset:samplesoccupation, education, gender, income

Filters

Filter data with WHERE:

WHERE Filter

TABLE WHERE gender = 'Male' ROWS occupation * income.sum;

Dataset:samplesoccupation, education, gender, income

Data Sources

TPLm queries a Malloy source, which can be defined from a CSV, Parquet file, or database table.

Specifying the Source in TPL

Use the FROM clause to specify which Malloy source to query:

TABLE FROM sales ROWS region * revenue.sum COLS quarter;

How It Works

1. Malloy Source Definition - Defines the raw data table and any computed dimensions/measures
2. FROM Clause - Tells TPLm which source to query
3. Generated Query - TPLm compiles to run: sales -> { ... }

Example Malloy Source

source: sales is duckdb.table('data/sales.csv') extend {
  dimension:
    quarter is concat('Q', floor((month - 1) / 3 + 1)::string)

  measure:
    total_revenue is revenue.sum()
}

With this source, your TPL can reference quarter and total_revenue:

TABLE FROM sales ROWS region * total_revenue COLS quarter;

See the Quick Start guide for complete examples.

Next Steps

Explore detailed examples:

• Core Concepts - Nesting, concatenation, aggregates
• Totals - Row and column totals with ALL
• Limits - Top N by value
• Percentages - ACROSS for ratios
• Formatting - Number formats
• Advanced - Full-featured tables