What Is Malloy
Overview
Malloy's render provides a variety of ways to render numeric values. Tagging a number will change how the number is displayed in the result set but the underlying value doesn't change.
The following examples that follow use the Malloy semantic data model below.
source: airports is duckdb.table('../data/airports.parquet') extend { dimension: name is concat(code, ' - ', full_name) measure: airport_count is count() } source: flights is duckdb.table('../data/flights.parquet') extend { join_one: orig is airports on origin = orig.code join_one: dest is airports on destination = dest.code measure: flight_count is count() }
# percent
Carriers as percentage of flights
run: flights -> { group_by: carrier # percent aggregate: percent_of_flights is flight_count / all(flight_count) }
[ { "carrier": "WN", "percent_of_flights": 0.2573783375431738 }, { "carrier": "US", "percent_of_flights": 0.10928088577750582 }, { "carrier": "AA", "percent_of_flights": 0.10027347046489979 }, { "carrier": "NW", "percent_of_flights": 0.09738216554968143 }, { "carrier": "UA", "percent_of_flights": 0.09499546149228454 }, { "carrier": "DL", "percent_of_flights": 0.09317715840116929 }, { "carrier": "RU", "percent_of_flights": 0.046614679244954715 }, { "carrier": "MQ", "percent_of_flights": 0.046020178234302996 }, { "carrier": "EV", "percent_of_flights": 0.04573017774130216 }, { "carrier": "HP", "percent_of_flights": 0.028275048067581715 }, { "carrier": "AS", "percent_of_flights": 0.024513741673360845 }, { "carrier": "CO", "percent_of_flights": 0.020703135195329833 }, { "carrier": "B6", "percent_of_flights": 0.01404182387110058 }, { "carrier": "OH", "percent_of_flights": 0.012818021790637044 }, { "carrier": "TZ", "percent_of_flights": 0.00879571495271542 } ]
WITH __stage0 AS ( SELECT group_set, CASE WHEN group_set=1 THEN base."carrier" END as "carrier__1", (CASE WHEN group_set=1 THEN COUNT(1) END)*1.0/MAX((CASE WHEN group_set=0 THEN COUNT(1) END)) OVER () as "percent_of_flights__1" FROM '../data/flights.parquet' as base CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set GROUP BY 1,2 ) SELECT "carrier__1" as "carrier", MAX(CASE WHEN group_set=1 THEN "percent_of_flights__1" END) as "percent_of_flights" FROM __stage0 WHERE group_set NOT IN (0) GROUP BY 1 ORDER BY 2 desc NULLS LAST
# number
Malloy uses LookML's (Excel) string definitions for formatting numbers.
# number="0" # Integer (123) # number="*00#" # Integer zero-padded to 3 places (001) # number="0 \" String\"" # Integer followed by a string (123 String) # Note \"String\" can be replaced with any other word # number="0.##" # Number up to 2 decimals (1. or 1.2 or 1.23) # number="0.00" # Number with exactly 2 decimals (1.23) # number="*00#.00" # Number zero-padded to 3 places and exactly 2 decimals (001.23) # number="#,##0" # Number with comma between thousands (1,234) # number="#,##0.00" # Number with comma between thousands and 2 decimals (1,234.00) # number="0.000,,\" M\"" # Number in millions with 3 decimals (1.234 M) # Note division by 1 million happens automatically # number="0.000,\" K\"" # Number in thousands with 3 decimals (1.234 K) # Note division by 1 thousand happens automatically # number="$0" # Dollars with 0 decimals ($123) # number="$0.00" # Dollars with 2 decimals ($123.00) # number="\"€\"0" # Euros with 0 decimals (€123) # number="$#,##0.00" # Dollars with comma btwn thousands and 2 decimals ($1,234.00) # number="$#.00;($#.00)" # Dollars with 2 decimals, positive values displayed # normally, negative values wrapped in parenthesis # number="0\%" # Display as percent with 0 decimals (1 becomes 1%) # number="0.00\%" # Display as percent with 2 decimals (1 becomes 1.00%) # number="0%" # Convert to percent with 0 decimals (.01 becomes 1%) # format="0.00%" # Convert to percent with 2 decimals (.01 becomes 1.00%)
run: flights -> { // tag a single element aggregate: # number="0" `integer` is flight_count // tag multiple elements at once. # number="$#,##0;($#,##0)" aggregate: dollars is flight_count neg_dollars is 0 - flight_count }
[ { "integer": 344827, "dollars": 344827, "neg_dollars": -344827 } ]
SELECT (COUNT(1)) as "integer", (COUNT(1)) as "dollars", 0-(COUNT(1)) as "neg_dollars" FROM '../data/flights.parquet' as base
Durations
The # duration renderer interprets a value as a number of seconds and renders it as a human-adjusted duration. Other units can be specified like # duration="minutes", with possible units of "nanoseconds", "microseconds", "milliseconds", "seconds", "minutes", "hours", and "days".
run: flights -> { group_by: dep_date is dep_time.day # duration="minutes" aggregate: longest_flight_time is max(flight_time) total_flight_time is flight_time.sum() aggregate: flight_count limit: 20 }
[ { "dep_date": "2005-12-31T00:00:00.000Z", "longest_flight_time": 362, "total_flight_time": 17874, "flight_count": 175 }, { "dep_date": "2005-12-30T00:00:00.000Z", "longest_flight_time": 464, "total_flight_time": 21054, "flight_count": 188 }, { "dep_date": "2005-12-29T00:00:00.000Z", "longest_flight_time": 485, "total_flight_time": 19923, "flight_count": 186 }, { "dep_date": "2005-12-28T00:00:00.000Z", "longest_flight_time": 440, "total_flight_time": 20367, "flight_count": 197 }, { "dep_date": "2005-12-27T00:00:00.000Z", "longest_flight_time": 480, "total_flight_time": 20381, "flight_count": 197 }, { "dep_date": "2005-12-26T00:00:00.000Z", "longest_flight_time": 480, "total_flight_time": 19683, "flight_count": 194 }, { "dep_date": "2005-12-25T00:00:00.000Z", "longest_flight_time": 1920, "total_flight_time": 19037, "flight_count": 167 }, { "dep_date": "2005-12-24T00:00:00.000Z", "longest_flight_time": 489, "total_flight_time": 17130, "flight_count": 159 }, { "dep_date": "2005-12-23T00:00:00.000Z", "longest_flight_time": 467, "total_flight_time": 20241, "flight_count": 201 }, { "dep_date": "2005-12-22T00:00:00.000Z", "longest_flight_time": 466, "total_flight_time": 19360, "flight_count": 200 }, { "dep_date": "2005-12-21T00:00:00.000Z", "longest_flight_time": 321, "total_flight_time": 20181, "flight_count": 192 }, { "dep_date": "2005-12-20T00:00:00.000Z", "longest_flight_time": 449, "total_flight_time": 20855, "flight_count": 203 }, { "dep_date": "2005-12-19T00:00:00.000Z", "longest_flight_time": 346, "total_flight_time": 18514, "flight_count": 207 }, { "dep_date": "2005-12-18T00:00:00.000Z", "longest_flight_time": 464, "total_flight_time": 20605, "flight_count": 179 }, { "dep_date": "2005-12-17T00:00:00.000Z", "longest_flight_time": 319, "total_flight_time": 15966, "flight_count": 139 }, { "dep_date": "2005-12-16T00:00:00.000Z", "longest_flight_time": 455, "total_flight_time": 22007, "flight_count": 195 }, { "dep_date": "2005-12-15T00:00:00.000Z", "longest_flight_time": 412, "total_flight_time": 20679, "flight_count": 204 }, { "dep_date": "2005-12-14T00:00:00.000Z", "longest_flight_time": 455, "total_flight_time": 21639, "flight_count": 193 }, { "dep_date": "2005-12-13T00:00:00.000Z", "longest_flight_time": 326, "total_flight_time": 19801, "flight_count": 190 }, { "dep_date": "2005-12-12T00:00:00.000Z", "longest_flight_time": 449, "total_flight_time": 19841, "flight_count": 176 } ]
SELECT DATE_TRUNC('day', base."dep_time") as "dep_date", max(base."flight_time") as "longest_flight_time", COALESCE(SUM(base."flight_time"),0) as "total_flight_time", COUNT(1) as "flight_count" FROM '../data/flights.parquet' as base GROUP BY 1 ORDER BY 1 desc NULLS LAST LIMIT 20