Skip to main content
DocumentationLibraryReference
On this page

Kater CLI Make and compare new

kater new field

Makes a new field in a saved query, as the new-field form in Kater does.

You start with a field, turn it into something such as a running total, set it up, and name it. Kater shows the field’s Kater SQL, its definition and its first rows before it saves anything.

Examples

Terminal window
# Go to the ad_reporting workspace in your local copy of Kater content
cd connections/snowflake_prod_connection/workspaces/ad_reporting
# Make a field step by step, with Kater asking for each step
kater new field --path query_contracts/paid_media_spend_trend.sql
# List what sum__spend can turn into, and make nothing
kater new field --path query_contracts/paid_media_spend_trend.sql --from sum__spend --list
# Add the percent change of spend from the month before
kater new field --path query_contracts/paid_media_spend_trend.sql --from sum__spend --make previous_period --set output=percent_change --write
# Add return on ad spend, as a measure written in Kater SQL
kater new field --path query_contracts/spend_conversion_value_by_platform.sql --sql "EVAL(ad_reporting.sum__conversions_value) / NULLIF(EVAL(ad_reporting.sum__spend), 0)" --kind measure --label "Return on ad spend" --write
# Add the same ratio as an Excel formula
kater new field --path query_contracts/spend_conversion_value_by_platform.sql --excel "=[@sum__conversions_value]/[@sum__spend]" --label "Return on ad spend" --write

Options

OptionDescription
--path <file>Required. The query file to add the field to, by its path from the folder you are in.
--model <name>The query in --path, by its name, when the file holds more than one.
--text-file <file>Query text to use in place of the saved file, such as an editor’s unsaved changes. - reads it from standard input.
--kind <kind>What to make: measure, calculation, dimension or filter. In a terminal, it starts as calculation. Elsewhere it follows from --make, and --sql makes a calculation unless you give a kind.
--from <field>Step 1, Start with: a field by its name, or as alias.name, such as sum__spend. It can be a field of a source the query reads, or one the query computes.
--make <choice>Step 2, Turn it into: a recipe, such as running_total or previous_period, or an operation from the field’s menu, such as comparisons.previous_period. It needs --from.
--set <name>=<value>Step 3, Settings: one setting, such as output=percent_change. A field setting takes field names, separated by commas for several. A choice takes its value or its words. How far back takes a number of periods back. Any other value can be JSON. It needs --make. You can give it more than one time.
--sql <expression>The field as a Kater SQL expression, in place of --from and --make. Put EVAL around a measure inside it, such as EVAL(ad_reporting.sum__spend).
--excel <formula>The field as an Excel formula, in place of --from and --make, or --sql. A reference is a column’s letter and a row, such as =B5-C5, or a column’s field name, such as =[@sum__conversions_value]/[@sum__spend]. A formula field is always a custom calculation.
--row <n>The row of the result that --excel is written on. A letter reference’s row number is read against it: on row 5, B5 is this row and B4 the row above. The default is 1.
--label <text>Step 4, Name it: the name that Kater shows on screen. Without it, the writer labels the field.
--name <name>The field’s SQL name. Without it, the writer names the field.
--description <text>What the field means, in a sentence or two.
--input <name>=<value>A parameter value for the preview, such as is_within_last__union__date_day=6. The query’s own filters are on, and every other parameter takes its default. You can give it more than one time.
--listShows the choices of the step that the options reach, in place of making the field. That is the fields to start with, what --from can turn into, or the settings and names of --make.
--no-inputNever asks, even in a terminal. A step that the options leave open is answered with its choices, and exit code 0.
--limit <n>How many rows the preview shows, from 1 to 1,000. The default is 10.
--no-rowsWrites and describes the field, and does not run the query.
--writeAdds the field to the query. Without it, a terminal asks first, and anywhere else nothing is saved.

It also takes the global options --json, --kater-root, --scope and --help.

The four steps

StepOptionExample
Start with--from FIELD--from sum__spend
Turn it into--make CHOICE: a recipe, or an operation from the field’s menu--make running_total, --make comparisons.previous_period
Settings--set NAME=VALUE, one for each setting--set output=percent_change
Name it--label, --name, --description--label "Spend change"

In a terminal and in a script

  • In a terminal, it asks for each step that the options leave out, numbered as the form numbers them. Enter keeps the default, a number selects a choice, and - leaves an optional step empty. It previews the field, asks before it adds it, and prints the one command that makes the same field.
  • Anywhere else, or with --json or --no-input, it never asks. A step left open gets that step’s choices and exit code 0. A script then adds the next option. The choices are field.options, as JSON when the output is not a terminal.
  • --list answers any step and makes nothing.
  • Without --write, a script saves nothing.

The preview

The preview runs with the query’s own filters on, and every parameter at its default. --input sets one parameter.

Options that go together

  • --make needs --from, and --set needs --make.
  • --sql takes the place of --from, --make and --set.
  • --excel takes the place of --sql, --from, --make, --set and --kind.
  • --list does not go with --sql, --excel or --write.
  • A filter cannot come from --sql.

See also