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. If a dimension and a column have the same name, qualified reads such as T.X resolve the dimension first. Rename the dimension or expose the column through an alias when both names are needed.

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 - 2026