[] 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"

Composite keys

When the primary dimension is a tuple (for example from single by), the lookup operator accepts multiple keys in the same order as the tuple.

table Lines = with
  [| as A, as B, as V |]
  [| "x", 1, 10 |]
  [| "x", 2, 20 |]
  [| "y", 1, 30 |]

table Dict[ab] = single by [Lines.A, Lines.B]
Dict.A, Dict.B = ab
Dict.V = single(Lines.V)

table Orders = with
  [| as A, as B |]
  [| "x", 2 |]
  [| "y", 1 |]

Orders.V = Dict.V[Orders.A, Orders.B]

show table "" with Orders.A, Orders.B, Orders.V

Missing keys

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

Lookups whose result is an enum, an embedding, or a tuple containing one of those types must provide an explicit default.

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

Lookups inside loops

Inside each, the lookup key may depend on the current iteration, even for text keys. The probed vector may also be assigned earlier in the same loop iteration.

table Items[id] = with
  [| as id, as Key |]
  [| "1", "A" |]
  [| "2", "B" |]
  [| "3", "D" |]
  [| "4", "A" |]

table Seek[K] = with
  [| as K, as X |]
  [| "A", 1 |]
  [| "B", 2 |]
  [| "C", 3 |]

expect table Seek small 3
n = 0
Items.B = each Items scan id
  keep n
  n = n + 1
  Seek.Y = Seek.X + n
  return Seek.Y[Items.Key] default 42

Inside for, iteration-dependent lookup keys are also supported, but only for fixed-width key types. Iteration-dependent text keys remain rejected in for.

table Items[id] = with
  [| as id, as Key |]
  [| "1", 1 |]
  [| "2", 2 |]
  [| "3", 4 |]

table Seek[K] = with
  [| as K, as X |]
  [| 1, 10 |]
  [| 2, 20 |]
  [| 3, 30 |]

expect table Seek small 3
Items.B = for Key in Items.Key
  return Seek.X[Key] default 42