Navigation :

cross

cross, table creation

The cross keyword creates a table as the cartesian product of two tables. The resulting table has two primary dimensions, obtained from the primary dimensions of the left and right tables respectively.

table Products[product] = with
  [| as product, as Price |]
  [| "shirt",  11.25 |]
  [| "pant",   25.75 |]
  [| "cap",     5.75 |]

table Colors[color] = with
  [| as color |]
  [| "white" |]
  [| "black" |]
  [| "blue"  |]
  [| "red"   |]

table Variants = cross(Products, Colors)

show table "All variants" with
  product
  color
  Products.Price

The right source table should be small. Both source tables must be distinct, have a single primary dimension, and cannot be Scalar or another cross table. By default, cross tables have a maximum size of 100m lines unless an explicit max constraint is provided.

Cross tables also act as the common table for expressions that combine unrelated tables. The cross table must be declared explicitly.

table R = extend.range(2)
table S = extend.range(3)

table RS = cross(R, S)
s = sum((1 into R) + (1 into S))

show scalar "sum" with s // 12

When multiple tables are involved in an expression, Envision selects a common table and broadcasts the other inputs into it. If exactly two tables are involved, neither can broadcast to the other, and a single cross table exists for that pair, the cross table is selected. If multiple common tables are possible, the leftmost table in the expression is selected.

Cross tables can be aggregated back into their source tables.

table R = extend.range(2)
table S = extend.range(3)

table RS = cross(R, S)
RS.A = R.N
RS.B = S.N

R.Total = sum(RS.A + RS.B)
S.Total = sum(RS.A + RS.B)

show table "R" with R.Total
show table "S" with S.Total

Advanced remark: In SQL, this mechanism is known as a cartesian join.

See also union tables when you want to concatenate rows instead of producing a cartesian product.

cross by at, table creation

The cross-by-at creates a table as the series of cartesian products from the two tables, with one cartesian product per group. The primary dimension of the new table is the concatenation of the primary dimensions of the left and right tables.

table T = with
  [| as A, as B |]
  [| "a",  1 |]
  [| "b",  1 |]
  [| "c",  2 |]
  [| "d",  2 |]

table U = with
  [| as B, as C |]
  [| 1, "x" |]
  [| 1, "y" |]
  [| 2, "z" |]

table TU = cross(T, U) by U.B at T.B
TU.A = T.A
TU.C = U.C

show table "TU" with
  TU.A
  TU.C

Advanced remark: In SQL, this mechanism is known as an inner join.

Transposed cross tables

The ordering of source tables matters. Two cross tables may exist with the same sources in opposite order, and assignments between them transpose the data. When both cross tables exist, explicit into removes ambiguity.

table R = extend.range(3)
table S = extend.range(4)

table RS = cross(R, S)
table SR = cross(S, R)

RS.N = (R.N into RS) + S.N
SR.N = RS.N

show table "RS" with
  R.N into RS as "R"
  S.N as "S"
  RS.N

show table "SR" with
  R.N into SR as "R"
  S.N as "S"
  SR.N

Filtering cross tables

Cross tables cannot be filtered with a where block or a filtered assignment. To filter, create a separate table with table T = where ...; the result is a regular table and is no longer a complete cartesian product.

table R = extend.range(2)
table S = extend.range(3)

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

table RSf = where RS.N > 3
RSf.N = RS.N

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

Filtering a source table preserves the cross property within the filtered scope. The cross table reflects the filtered source lines but remains a cross table:

table R = extend.range(2)
table S = extend.range(3)

table RS = cross(R, S)

where R.N == 1
  RS.A = R.N
  RS.B = S.N
  show table "cross" with RS.A, RS.B

Transitive broadcasting

Any table that can broadcast to a source table can broadcast to the cross table by importing the shared dimension.

table R = extend.range(2)
table S1[s1] = extend.range(3)
table S2 small 100 = extend.range(S1.N)

table RS2 = cross(R, S2)
RS2.s1 = S2.s1

RS2.A = R.N
RS2.B = S1.N

show table "cross" with RS2.A, RS2.B

Secondary dimensions on cross tables

Assigning a dimension vector to a cross table adds it as a secondary dimension when the compiler can prove consistency.

table Colors[color] = with
  [| as color, as Code |]
  [| "red",    0       |]
  [| "blue",   1       |]
  [| "green",  2       |]

table Products = with
  [| as Label, as Col |]
  [| "Hat",    "red"  |]
  [| "Shirt",  "blue" |]

expect Products.color = Products.Col

table Locations = with
  [| as City |]
  [| "New York" |]
  [| "Paris"    |]

table SKUs = cross(Locations, Products)
SKUs.color = Products.color
SKUs.Code = Colors.Code

show table "SKUs" with Locations.City, Products.Label, SKUs.Code
User Contributed Notes
0 notes + add a note

© Lokad 2014 - 2025