Input · Make your own data

DuckDB Query

Write your own SQL query for anything the other nodes don't cover. A wired-in table is available to the query as _input. Excel's cash-flow functions such as NPV and IRR take a column here, e.g. NPV(0.08, list(cashflow ORDER BY period)).

Also known as: Custom SQL

How it works

Write a DuckDB SELECT in SQL query. When a table is wired into the node it is available as _input; with nothing wired the query runs on its own, for example over a file path. The query runs when you press Run query, never on a keystroke. Geometry CRS declares the coordinate system of any geometry the query builds, such as EPSG:4326, so metric nodes like Buffer can run on it; blank inherits the input's CRS or leaves it unknown. Writing ${{ param.<column> }} in the query adds a params port, and the value is read from the first row of the table wired into it.

Inputs and outputs

Settings

sql: string, crs?: string  // a DuckDB SELECT; an optional upstream input is available as the table _input, e.g. "SELECT region, count(*) AS n FROM _input GROUP BY region". crs ("EPSG:4326") declares the CRS of geometry the query builds, so metric Buffer / Trade Areas downstream can run; blank = inherited from the input, or unknown

Example

A per-region total that no built-in node quite gives you, over the wired-in orders: SELECT region, count(*) AS n, sum(amount) AS total FROM _input WHERE status = 'paid' GROUP BY region. Press Run query and the Data tab shows one row per region.

More in Make your own data

• Constant Table — A small table you type in by hand, with named, typed columns. Handy as a lookup list, or as the list of items a loop goes through.
• Generate Series — Creates a one-column table of numbers, dates or timestamps from a start to a stop at a fixed step, such as every day or every 15 minutes. Useful as a calendar to join facts onto.