each

each, keyword

The each keyword offers an iteration mechanism against an observation table. Iterations are not ordered.

table Obs = with
  [| as N |]
  [| 1 |]
  [| 2 |]
  [| 3 |]

Obs.Cpy = each Obs
  cpy = Obs.N + 1
  return cpy

show table "" with Obs.N, Obs.Cpy

each .. scan , keyword

The each .. scan keywords offer an iteration mechanism against an observation table. Iterations are ordered based on the argument passed to scan. The scan expression must depend only on data available before the loop starts. Expressions computed inside the loop are rejected to avoid rebuilding indices per iteration.

table Obs = with
  [| date(2021, 1, 1) as Date, 13 as Quantity |]
  [| date(2021, 2, 1)       ,  11             |]
  [| date(2021, 3, 1)       ,  17             |]
  [| date(2021, 4, 1)       ,  18             |]
  [| date(2021, 5, 1)       ,  16             |]

Best = 0

Obs.BestSoFar = each Obs scan Obs.Date
  keep Best
  NewBest = max(Best, Obs.Quantity)
  Best = NewBest
  return NewBest

show table "" with Obs.Date, Obs.BestSoFar

Table diagram

each uses the table diagram to decide how tables are accessed in the loop:

• Iteration table: the table after each.
• Upstream table: broadcasts into the iteration table.
• Downstream table: receives broadcasts from the iteration table (not allowed).
• Upstream-cross table: a cross table whose left side is the iteration table or upstream; only allowed when the right side is a full table and either small or equal to the iteration table.
• Full table: any other table; allowed only if small.

Reads from upstream tables return the scalar value matching the current iteration line. Writes to the iteration table or upstream tables are forbidden; use keep Upstream.X as X to update upstream values through a scalar alias. Writing to upstream-cross tables is forbidden. Broadcasting into the iteration table or upstream tables is forbidden.

The keep statements must appear first in the block and refer to variables defined before the loop. each .. scan requires at least one keep. desc reverses the scan order.

Upstream vectors must be kept as scalars and updated through an alias:

table Items[id] = with
  [| as Id, as X, as Cat |]
  [| 1, 1, "A" |]
  [| 2, 2, "B" |]

table Categories[cat] = by Items.Cat
Categories.X = 0

Items.B = each Items scan Items.Cat
  keep Categories.X as X
  X = Items.X + X
  return X

Return-less blocks

If only the final keep values matter, return can be omitted:

table Currencies = with
  [| as Code |]
  [| "EUR"   |]
  [| "JPY"   |]
  [| "USD"   |]

Sep = ""
List = ""
each Currencies scan Currencies.Code
  keep Sep
  keep List
  List = "\{List}\{Sep}\{Currencies.Code}"
  Sep = ", "

show scalar "" with List

auto ordering

scan auto follows the primary dimension ordering:

table T = extend.range(3)
x = 0
T.X = each T scan auto
  keep x
  x = T.N - x
  return x

Any-order blocks

scan T.* allows arbitrary ordering:

table Obs = with
  [| as X |]
  [| 42   |]
  [| 41   |]
  [| 45   |]

myMin = 1B
myMax = -(1B)
each Obs scan Obs.*
  keep myMin
  keep myMax
  myMin = min(myMin, Obs.X)
  myMax = max(myMax, Obs.X)

each .. when

when filters iterations and is only allowed on return-less blocks:

table T = extend.range(5)
s = 0
each T scan auto when T.N mod 2 == 1
  keep s
  s = s + T.N

Reasons to use each

Use each when you need keep variables in upstream tables or need to read from upstream-cross tables. Otherwise prefer for.