DataTables allow subsets of data to be selected and aggregated. They also allow calculated columns to be created on the fly when creating new DataTables or object arrays.
var data = [ {product: "apples", order: "PRN001", quantity: 10, price: 5.5}, {product: "oranges", order: "PRN002", quantity: 15, price: 6}, {product: "bananas", order: "PRN002", quantity: 20, price: 7.5} ]; var table = new eb.data.DataTable(data); // select two columns from the table and create a new one table = table.select("product", "price").table();
To format columns, reference the column in the column
method and then use the format
method. Number formatting is supported using P1
and N1
for percentage and numeric formatting respectively. Change 1
to any decimal place.
// format price with 1 decimal place table = table.select("product").column("price").format("N1").table();
Use as
to give any column a descriptive title. This will be set into the Title
property for the column in the result table.
// change the title for product table = table.select().column("product").as("Product Title").table();
Calculated Columns
New columns can be created using the following aggregation methods.
- avg
- count
- distinctCount
- max
- min
- sum
var data = [ {product: "apples", order: "PRN001", quantity: 10, price: 5.5}, {product: "oranges", order: "PRN002", quantity: 15, price: 6}, {product: "bananas", order: "PRN002", quantity: 20, price: 7.5} ]; // total quantity with a new title and average price table = table.select() .sum("quantity").as("Total Quantity") .avg("price").format("N1") .table();
Aggregates can also be used with implicit grouping by selecting other columns to be included in the result. The example below includes the order
column so the calculated column Total Quantity
is broken out by order
.
// select order and Total Quantity table = table.select("order").sum("quantity").as("Total Quantity").table();
Calculated columns can also be created using custom formulas by using the calc
method.
// return the quantity column times 10 in a new column using the sum aggregation method table = table.select() .sum().calc(function(row){ return row["quantity"] * 10; }) .as("More Quantity").table();