Malloy Documentation
search
previousNumbers
Pivoted Tablesnext

Links

VSCode document

Results can annotated so the results are be rendered with links to external urls.

document
source: flights is duckdb.table('../data/flights.parquet') extend {
  measure: flight_count is count()
  join_one: carriers is duckdb.table('../data/carriers.parquet') on carrier=carriers.code
}

Simple link annotation

If you have a column which is a url, you can turn it into a link with a simple '# link` annotation

document
run: flights -> {
    # link
    group_by: flight_link is concat("https://www.flightsfrom.com/", origin)
    aggregate: flight_count
    limit: 5
}
QUERY RESULTS 
[
  {
    "flight_link": "https://www.flightsfrom.com/ATL",
    "flight_count": 17875
  },
  {
    "flight_link": "https://www.flightsfrom.com/DFW",
    "flight_count": 17782
  },
  {
    "flight_link": "https://www.flightsfrom.com/ORD",
    "flight_count": 14214
  },
  {
    "flight_link": "https://www.flightsfrom.com/PHX",
    "flight_count": 12476
  },
  {
    "flight_link": "https://www.flightsfrom.com/LAS",
    "flight_count": 11096
  }
]
SELECT 
   CONCAT('https://www.flightsfrom.com/',base."origin") as "flight_link",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 5

Link url_template, append to the end

Often the think you want on the template is at the end of the string. Create links to flightsfrom.com.

document
run: flights -> {
    # link {url_template='https://www.flightsfrom.com/'}
    group_by: origin
    aggregate: flight_count
    limit: 5
}
QUERY RESULTS 
[
  {
    "origin": "ATL",
    "flight_count": 17875
  },
  {
    "origin": "DFW",
    "flight_count": 17782
  },
  {
    "origin": "ORD",
    "flight_count": 14214
  },
  {
    "origin": "PHX",
    "flight_count": 12476
  },
  {
    "origin": "LAS",
    "flight_count": 11096
  }
]
SELECT 
   base."origin" as "origin",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 5

Link url_template, substition

Sometimes the variable part of the link needs to be substitued in the middle of the string. In this case, the output columns value inserted where $$ appears in the url_template.

Flights to SJC with links to 'flightsfrom.com'.

document
run: flights -> {
    where: destination = 'SJC'
    # link {url_template='https://www.flightsfrom.com/$$-SJC'}
    group_by: origin
    aggregate: flight_count
    limit: 5
}
QUERY RESULTS 
[
  {
    "origin": "LAX",
    "flight_count": 495
  },
  {
    "origin": "SAN",
    "flight_count": 423
  },
  {
    "origin": "SEA",
    "flight_count": 386
  },
  {
    "origin": "PHX",
    "flight_count": 289
  },
  {
    "origin": "LAS",
    "flight_count": 283
  }
]
SELECT 
   base."origin" as "origin",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
WHERE base."destination"='SJC'
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 5

Link with key column coming from another field.

In the example below we want to show the Carrier's nickname with a link using the carriers code. We can do this by including the carrier code in the query, hiding it with a #hidden tag and referencing carrier using field parameter in the # link tag.

document
run: flights -> {
    # link {url_template='https://www.flightsfrom.com/$$' field=carrier}
    group_by: 
      carriers.nickname
      # hidden
      carrier
    aggregate: flight_count
    limit: 5
}
QUERY RESULTS 
[
  {
    "nickname": "Southwest",
    "carrier": "WN",
    "flight_count": 88751
  },
  {
    "nickname": "USAir",
    "carrier": "US",
    "flight_count": 37683
  },
  {
    "nickname": "American",
    "carrier": "AA",
    "flight_count": 34577
  },
  {
    "nickname": "Northwest",
    "carrier": "NW",
    "flight_count": 33580
  },
  {
    "nickname": "United",
    "carrier": "UA",
    "flight_count": 32757
  }
]
SELECT 
   carriers_0."nickname" as "nickname",
   base."carrier" as "carrier",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON base."carrier"=carriers_0."code"
GROUP BY 1,2
ORDER BY 3 desc NULLS LAST
LIMIT 5