Navigation :

table

table, keyword

Defines a table and its primary dimension. Each table has one primary dimension, except Scalar (none) and cross tables (two).

Example

table Products[sku] = with
  [| as sku, as Price |]
  [| "SKU-1", 10.50 |]
  [| "SKU-2", 12.00 |]

show table "Products" with
  sku
  Products.Price

This outputs the following table:

sku Price
SKU-1 10.5
SKU-2 12

Remarks

Primary dimension values must be unique; duplicates fail at runtime. by creates a group table, while where filters rows from another table. min and max size constraints can be attached to a table declaration. The dimension name in brackets only labels the primary dimension inferred from the right-hand side. Composite primary dimensions cannot be named; omit the brackets when the primary key is a tuple.

Table creation forms

Tables can be created from comprehensions or from generators such as by and extend.range.

table Items[id] = with
  [| as id, as Qty |]
  [| "A", 2 |]
  [| "B", 1 |]

table Lines = extend.range(Items.Qty)
Lines.id = Items.id

table Groups = by Items.id
Groups.Total = sum(Items.Qty)

show table "Lines" with
  Lines.id
  Lines.N

show table "Groups" with
  Groups.id
  Groups.Total

For a composite primary dimension, use a tuple in the by clause and omit the dimension name:

table SKUs = with
  [| as Loc, as Ref, as OnHand |]
  [| "P1", "A", 10 |]
  [| "P1", "B", 5 |]
  [| "P2", "A", 7 |]

table SKUs = single by [SKUs.Loc, SKUs.Ref]

show table "SKUs" with
  SKUs.OnHand

table, tile type

The table tile renders tabular data and is the only tile that can export data via the write option.

Example

table Sales = with
  [| as Product, as Units |]
  [| "Hat", 3 |]
  [| "Hat", 7 |]
  [| "Shoe", 5 |]

show table "Units by product" with
  Sales.Product
  sum(Sales.Units) as "Units"
  group by Sales.Product
  order by sum(Sales.Units) desc

This outputs the following table:

Product Units
Hat 10
Shoe 5

Tile-level options

Tile-level options appear after the title and before with.

table Products = with
  [| as Product, as Price, as StartDate |]
  [| "Hat", 42.25, date(2022, 1, 1) |]
  [| "Shoe", 49.90, date(2022, 2, 17) |]

show table "Products export" date:"MM-dd-yyyy" write:"/sample/products.csv" with
  Products.Product
  Products.Price excelFormat:"#,##0.00"
  Products.StartDate write:false

This outputs the following table:

Product Price StartDate
Hat 42.25 2022-01-01
Shoe 49.9 2022-02-17

The list of options available are:

Sheet names can be specified by appending {Sheet} to the write path. Sheets are ordered by their appearance in the script.

Column-level options

The table tile supports options defined at the column level:

Exporting data

The write tile-level option exports the rendered table to files. Supported formats are:

Two tiles may not write to the same file path. Multiple write options can be used to export to several locations.

table Sales = with
  [| as Product, as Units |]
  [| "Hat", 3 |]
  [| "Hat", 7 |]
  [| "Shoe", 5 |]

show table "Exports" write:"/sample/sales.tsv" write:"/sample/sales.xlsx{Summary}" with
  Sales.Product
  sum(Sales.Units) as "Units"
  group by Sales.Product

Metadata for exports

Files produced by show table exports can carry documentation comments using ///. These comments are surfaced in the Envision editor when inspecting downstream variables that originate from the file. The same comment mechanism is used by write.

table Sales = with
  [| as Product, as Units |]
  [| "Hat", 3 |]
  [| "Hat", 7 |]
  [| "Shoe", 5 |]

/// Sales summary table
show table "Sales" write:"/sample/sales.tsv" with
  /// Product identifier
  Sales.Product
  /// Total units sold
  sum(Sales.Units) as "Units"
  group by Sales.Product

StyleCode

For generic StyleCode rules, see stylecode.

See also

User Contributed Notes
0 notes + add a note

© Lokad 2014 - 2025