[] lookup operator

The lookup operator [..] is used to request a value a vector by specifying a key that belongs to the primary dimension of the probed table.

Lookup keys must support equality with good performance: text, number, boolean, date, week, month, flagset, all enums, and the internal types ordinal, double, and long. Cross table lookups name both dimensions (for example r: 2, s: 3).

table Products[product] = with
  [| as product,   as Price |]
  [| "apple",      1.50     |]
  [| "pear",       1.30     |]
  [| "orange",     2.10     |]
  [| "clementine", 2.70     |]
 
table Selection = with
  [| as Choice |]
  [| "pear"    |]
  [| "orange"  |]
  [| "banana"  |] // missing

Selection.PriceOfSelection = Products.Price[Selection.Choice] // lookup operator

show table "Selection" with
  Selection.Choice
  Selection.PriceOfSelection as "Price"

When trying to access a missing key, the lookup operator returns the default value for the datatype:

table Products[product] = with
  [| as product,   as Price |]
  [| "apple",      1.50     |]
  [| "pear",       1.30     |]
  [| "orange",     2.10     |]
  [| "clementine", 2.70     |]
 
missingPrice = Products.Price["banana"] // 0 (default number value)

show scalar "" with missingPrice // 0

It is possible to force a runtime failure:

table Products[product] = with
  [| as product,   as Price |]
  [| "apple",      1.50     |]
  [| "pear",       1.30     |]
  [| "orange",     2.10     |]
  [| "clementine", 2.70     |]
 
missingPrice = Products.Price["banana"] default fail // 'Key not found in lookup.' (runtime error)

show scalar "" with missingPrice // never displayed

Or specify an explicit default value:

table Products[product] = with
  [| as product,   as Price |]
  [| "apple",      1.50     |]
  [| "pear",       1.30     |]
  [| "orange",     2.10     |]
  [| "clementine", 2.70     |]
 
missingPrice = Products.Price["banana"] default -1 // -1 when missing

show scalar "" with missingPrice // -1

Lookups on cross tables

Cross table lookups specify both dimension names.

table R[r] = with
  [| as r |]
  [| 1 |]
  [| 2 |]

table S[s] = with
  [| as s |]
  [| 1 |]
  [| 2 |]
  [| 3 |]

table RS = cross(R, S)
RS.N = r + s

n = RS.N[r: 2, s: 3]
show scalar "n" with n

Partial lookups on cross tables

Providing only one key returns a vector aligned with the other source table.

table R[r] = with
  [| as r |]
  [| 1 |]
  [| 2 |]

table S[s] = with
  [| as s |]
  [| 1 |]
  [| 2 |]
  [| 3 |]

table RS = cross(R, S)
RS.N = r + s

R.N = RS.N[s: 3]
S.N = RS.N[r: 2]

show table "R" with R.N
show table "S" with S.N

When a single key is provided and it belongs to a table that already carries the other dimension, the other dimension is inferred from that table:

table R[r] = with
  [| as r |]
  [| 1 |]
  [| 2 |]

table S[s] = with
  [| as s |]
  [| 10 |]
  [| 20 |]

table RS = cross(R, S)
RS.N = r + s

table U = with
  [| as RKey, as SKey |]
  [| 1, 10 |]
  [| 2, 20 |]

expect U.r = U.RKey
expect U.s = U.SKey

U.N = RS.N[s: U.s] // implicit r from U

show table "U" with U.RKey, U.SKey, U.N

If no dimension labels are provided in a cross table lookup, the rightmost dimension of the cross table is used.

Lag syntax

When a lookup key is a number literal, it acts as an offset against the primary dimension.

table T[day] = with
  [| as day, as X |]
  [| date(2024, 1, 1), 10 |]
  [| date(2024, 1, 2), 20 |]
  [| date(2024, 1, 3), 30 |]

T.Prev = T.X[-1] // same as T.X[day: T.day - 1]

show table "T" with
  day
  T.X
  T.Prev