A raw value often has too many values, or values that are too fine, to group by. The Derive menu turns a field into a new dimension that people can read: a map of values to labels, numeric buckets, or a flag that is yes or no. Each one is a KATER__ call. Each one reopens in the menu’s form, so you can change its settings later.

How it works

Right-click a field and select Derive. Each item opens the New dimension dialog, filled in. The dialog writes a DIMENSION over a KATER__ call. It makes a name and a label from the field, such as bucket__amount and “Amount (Bucket)”. You can type your own under Name it.

The date parts and periods since are on Dates and times. Whether a number is categorical or continuous decides which items it offers. See Classification.

Map values to labels

How many orders come from Europe, the Americas and the rest of the world? semantic.shop maps each region to a group.

1
  DIMENSION(
2
    'region_group',
3
    KATER__MAP(o.region, ('EMEA', 'Europe'), ('AMER', 'Americas'), default_label => 'Rest of World')
4
  ) AS "Region Group",

1
SELECT
2
  shop.region_group,
3
  shop.region,
4
  shop.count_distinct__order_id
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.region_group,
8
  shop.region
9
ORDER BY
10
  1,
11
  2

Every region that no pair names gets the default label. That includes the 64 orders with no region: a missing value matches no pair, so it goes to Rest of World, not to null.

• The default is Other. Leave out default_label, and every other value reads Other. A map of ('paid', 'Paid'), ('email', 'Email') over Channel gives Paid 762, Email 778 and Other 1,460.
• A pair matches one value exactly. To group many values, write a pair for each one.
• The labels are text. The new field holds text, and it is categorical.

Buckets

How do orders spread across small, medium and large amounts? semantic.shop cuts Amount at 25 and 100.

1
  DIMENSION('amount_band', KATER__BUCKET(o.amount, [25, 100])) AS "Amount Band"

1
SELECT
2
  shop.amount_band,
3
  shop.count_distinct__order_id,
4
  MEASURE('min__amount', KATER__MIN(shop.amount)) AS "Smallest Amount",
5
  MEASURE('max__amount', KATER__MAX(shop.amount)) AS "Largest Amount"
6
FROM workspace.shop AS shop
7
GROUP BY
8
  shop.amount_band
9
ORDER BY
10
  3

Two edges make three bands. The shop has 201 orders of exactly 25.00 and 88 of exactly 100.00.

• A value on an edge goes to the band above. Each band holds its lower edge, so 25.00 is in 25 – 100 and 100.00 is in ≥ 100.
• A missing value stays missing. The 31 orders with no amount have no band, so they group under null.
• Kater names the bands from the edges. The first band is < 25, the last is ≥ 100, and each band between reads 25 – 100.

Name the bands

To show your own names, give one name for each band, in order:

1
SELECT
2
  DIMENSION('amount_size', KATER__BUCKET(shop.amount, [25, 100], labels => ['Small', 'Medium', 'Large'])) AS "Amount Size",
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  1
7
ORDER BY
8
  2

Two edges need three names. With two names, the compiler refuses the call: “KATER__BUCKET: 2 edges make 3 buckets, so labels takes 3 labels.”

Flags

A flag is a yes-or-no dimension: a condition on one field. It shows as Yes or No. When the field is empty, the flag is null, except for Is Empty and Is Not Empty.

The Derive menu writes these. A filter has more conditions, such as KATER__IS_AT_LEAST, which the Derive menu does not offer. You can write KATER__IS_AT_LEAST as a flag by hand, as More than 100, or at least 100 does. See Filters and parameters for every condition.

Notes that mention a gift

How many orders ask for gift wrap? The orders dataset flags each order whose notes contain “gift”:

1
  DIMENSION('text_contains_gift__notes', KATER__TEXT_CONTAINS(orders.notes, 'gift')) AS "Notes Contain gift",

Pass the flag through in semantic.shop, so the workspace can group by it:

1
  o.text_contains_gift__notes,

1
SELECT
2
  shop.text_contains_gift__notes,
3
  shop.count_distinct__order_id
4
FROM workspace.shop AS shop
5
GROUP BY
6
  shop.text_contains_gift__notes
7
ORDER BY
8
  2

The 575 orders say “gift wrap please”. The 1,244 orders with no notes get null, not No, because there is no text to search.

Customers in the US or Canada

How many orders and customers are in the US or Canada? The customers dataset flags the two countries:

1
  DIMENSION('is_one_of_us_ca__country', KATER__IS_ONE_OF(customers.country, 'US', 'CA')) AS "Country Is US or CA",

Pass it through in semantic.shop:

1
  c.is_one_of_us_ca__country,

1
SELECT
2
  shop.is_one_of_us_ca__country,
3
  shop.count_distinct__order_id,
4
  shop.count_distinct__customer_id
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.is_one_of_us_ca__country
8
ORDER BY
9
  2

72 customers are in the US or Canada: 42 in the US and 30 in Canada. The 26 customers with no country have no flag.

One region, and no region

Which orders are from EMEA, and which have no region at all? Two flags in a query:

1
SELECT
2
  DIMENSION('is_equal_to_emea__region', KATER__IS_EQUAL_TO(shop.region, 'EMEA')) AS "Region Is EMEA",
3
  DIMENSION('is_null__region', KATER__IS_NULL(shop.region)) AS "Region Is Empty",
4
  shop.count_distinct__order_id
5
FROM workspace.shop AS shop
6
GROUP BY
7
  1,
8
  2
9
ORDER BY
10
  3

Region Is EMEA is null for the 64 orders with no region. Region Is Empty is never null: it says Yes for them.

More than 100, or at least 100

Do orders of exactly 100 count as large? Compare Is Greater Than with Is At Least.

1
SELECT
2
  DIMENSION('is_greater_than_100__amount', KATER__IS_GREATER_THAN(shop.amount, 100)) AS "Amount Is Greater Than 100",
3
  DIMENSION('is_at_least_100__amount', KATER__IS_AT_LEAST(shop.amount, 100)) AS "Amount Is At Least 100",
4
  shop.count_distinct__order_id
5
FROM workspace.shop AS shop
6
GROUP BY
7
  1,
8
  2
9
ORDER BY
10
  3

The 88 orders of exactly 100.00 are not greater than 100, but they are at least 100. Is At Least is not in the Derive menu, so this query writes it by hand.

Count a flag

How many orders in each region ask for gift wrap? The Aggregations menu on a flag offers Count True and Count False. Each one is a measure.

1
SELECT
2
  shop.region,
3
  shop.count_distinct__order_id,
4
  MEASURE('count_true__text_contains_gift__notes', KATER__COUNT_TRUE(shop.text_contains_gift__notes)) AS "Notes Contain gift (Count True)",
5
  MEASURE('count_false__text_contains_gift__notes', KATER__COUNT_FALSE(shop.text_contains_gift__notes)) AS "Notes Contain gift (Count False)"
6
FROM workspace.shop AS shop
7
GROUP BY
8
  shop.region
9
ORDER BY
10
  2 DESC

Count True and Count False do not add up to Orders. An order with no notes has a null flag, which is neither true nor false. See Measures for the other aggregations.

By hand

When no operation writes what you need, write a CASE. The orders dataset declares Order Size that way:

1
  DIMENSION('order_size', CASE WHEN orders.amount >= 100 THEN 'large' ELSE 'small' END) AS "Order Size",

Compare it with Amount Band, the bucket that cuts at the same 100:

1
SELECT
2
  shop.order_size,
3
  shop.amount_band,
4
  shop.count_distinct__order_id
5
FROM workspace.shop AS shop
6
GROUP BY
7
  shop.order_size,
8
  shop.amount_band
9
ORDER BY
10
  1,
11
  2

Both put an order of 100.00 in the upper group. But the CASE sends the 31 orders with no amount to ELSE, so they are small. The bucket keeps them null.

Use the operation when one writes what you need:

• It reopens in its form. The field’s menu opens the bucket with its edges and names, so you change a setting, not the SQL. A CASE written in the same shape as a bucket or a map reopens in that form too.
• It handles missing values the same way every time. A bucket and a flag keep an empty value null. A map sends it to the default label.
• It names and labels the field. The dialog writes a name such as group__region and a label such as “Region (Group)”, unless you type your own.

Rules

• A derived field is a new field. It needs a name that no field in scope has.
• A value on a bucket’s edge goes to the band above. Each band holds its lower edge.
• A missing value has no band and no flag. A bucket and a flag give null for it. Is Empty and Is Not Empty always give Yes or No. A map gives its default label.
• A map’s default is Other. Write default_label to set your own.
• Contains ignores letter case. KATER__TEXT_CONTAINS(orders.notes, 'gift') matches Gift and GIFT too, anywhere in the text.

Common errors