Skip to main content

# KATER__MAP

A label for each value you list, and one label for every other value.

{/* Written by apps/landing-page/scripts/docs/write_kater_functions.py from the compiler's function registry, kater_sql/function_docs.py and scripts/docs/sql-language.md. Do not edit it by hand: change those, then run the script. */}

**Family:** [Derive](https://kater.ai/docs/sql-reference/functions/derive/)

## Syntax

```sql
KATER__MAP(field, (value, label) [, ...] [, default_label => 'label'])
```

## Arguments

**Required**

| Argument | Description |
| --- | --- |
| `field` | The field whose values get labels. |
| `mappings` | Each value and the label it gets. One or more `(value, 'label')` pairs. |

**Optional**

| Argument | Description |
| --- | --- |
| `default_label` | The label for each value you do not list, and for an empty value. A value, or a parameter a reader supplies. It is `'Other'` when left out. |

## Returns

Text.

## Usage notes

- A value matches by equality, so `(NULL, 'label')` never matches. An empty value gets `default_label`.

## Example

This Sandbox query reads the sample shop's orders. The table shows the rows that Kater gave.

```sql {3,7}
SELECT
  orders.channel AS "Channel",
  KATER__MAP(orders.channel, ('paid', 'Paid media'), ('email', 'Owned'), ('web', 'Owned'), default_label => 'Offline') AS "Channel Group"
FROM kater_dataset.orders AS orders
GROUP BY
  orders.channel,
  KATER__MAP(orders.channel, ('paid', 'Paid media'), ('email', 'Owned'), ('web', 'Owned'), default_label => 'Offline')
ORDER BY
  1
```

| Channel | Channel Group |
| --- | --- |
| email | Owned |
| paid | Paid media |
| store | Offline |
| web | Owned |

## Compiles to

The call compiles to the same SQL on DuckDB and on Snowflake.

```sql
CASE
  WHEN orders.channel = 'paid'
  THEN 'Paid media'
  WHEN orders.channel = 'email'
  THEN 'Owned'
  WHEN orders.channel = 'web'
  THEN 'Owned'
  ELSE 'Offline'
END
```

## See also

- [Groups and flags](https://kater.ai/docs/model/fields/dimensions/groups-and-flags/)
- Other functions in [Derive](https://kater.ai/docs/sql-reference/functions/derive/): [`KATER__BUCKET`](https://kater.ai/docs/sql-reference/functions/kater__bucket/), [`KATER__PERIODS_SINCE`](https://kater.ai/docs/sql-reference/functions/kater__periods_since/)

On this page

Syntax
Arguments
Returns
Usage notes
Example
Compiles to
See also

Kater SQL Functions Derive

KATER__MAP

A label for each value you list, and one label for every other value.

Family: Derive

Syntax

1
KATER__MAP(field, (value, label) [, ...] [, default_label => 'label'])

Arguments

Required

Argument	Description
`field`	The field whose values get labels.
`mappings`	Each value and the label it gets. One or more `(value, 'label')` pairs.

Optional

Argument	Description
`default_label`	The label for each value you do not list, and for an empty value. A value, or a parameter a reader supplies. It is `'Other'` when left out.

Returns

Text.

Usage notes

A value matches by equality, so (NULL, 'label') never matches. An empty value gets default_label.

Example

This Sandbox query reads the sample shop’s orders. The table shows the rows that Kater gave.

1
SELECT
2
  orders.channel AS "Channel",
3
  KATER__MAP(orders.channel, ('paid', 'Paid media'), ('email', 'Owned'), ('web', 'Owned'), default_label => 'Offline') AS "Channel Group"
4
FROM kater_dataset.orders AS orders
5
GROUP BY
6
  orders.channel,
7
  KATER__MAP(orders.channel, ('paid', 'Paid media'), ('email', 'Owned'), ('web', 'Owned'), default_label => 'Offline')
8
ORDER BY
9
  1

Channel	Channel Group
email	Owned
paid	Paid media
store	Offline
web	Owned

Compiles to

The call compiles to the same SQL on DuckDB and on Snowflake.

1
CASE
2
  WHEN orders.channel = 'paid'
3
  THEN 'Paid media'
4
  WHEN orders.channel = 'email'
5
  THEN 'Owned'
6
  WHEN orders.channel = 'web'
7
  THEN 'Owned'
8
  ELSE 'Offline'
9
END

See also

Groups and flags
Other functions in Derive: KATER__BUCKET, KATER__PERIODS_SINCE