Select and Calculate
The following example selects two columns,
product
and price
, from the data table and creates a new data table using the table
function at the end of the method chain.let 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 }
];
let table = new wire.data.DataTable(data);
table = table.select("product", "price").table();
Columns have a number of attributes that can be set in the
select
method chain. In order to set these attributes, reference an individual column using the column
method and then use any number of the column attribute methods to set it's values.Use the
as
function to change the name of a field. In the example below, the field quantity
will be named number_of_skus
in the new table....
table = table.select().column("quantity").as("number_of_skus").table();
Use the
title
function to give any column a descriptive title. This will be set into the Title
property for the column in the resulting table....
table = table.select().column("product").title("Product Title").table();
A data table needs a descriptive
Title
value for any column. If one is not supplied, it will automatically create one. In doing so, it will parse camelCase or PascalCase field names and intelligently capitalize and space accordingly to come up with a friendly title.In the example below, a title for the
numberOfSkus
column is not supplied. In the new table, the title will automatically be Number Of Skus
....
table = table.select().column("quantity").as("numberOfSkus").table();
To format numeric columns, use the format method with a format specification. Valid values are C[N], P[N], N[N] where [N] is the number of decimal places.
The example below sets the formatting for the
price
column to currency one decimal place using the C1
parameter in the format
function. This value will be set into the Format
property for the column in the resulting table....
table = table.select("product").column("price").format("C1").table();
The format method can also accept a custom function in order to format data in the column.
The following example formats a date field called
date_field
using the third party library moment.js....
table = table.select().column("date_field").format((row) => {
return moment(row["date_field"]).format("MM-DD-YYY");
}).table();
Data tables allow calculated columns to be created on the fly.
The following creates two columns in a new data table that are the aggregates of columns in an existing data table. In the new table,
quantity
will be aggregated using sum
. It will have the same name and be formatted to numeric one decimal place using format
N1
. The price
column will be averaged using avg
and it's name changed to avg_price
. It will be formatted to currency one decimal place using format
C1
....
table = table.select()
.sum("quantity").format("N1")
.avg("price").as("avg_price").format("C1")
.table();
Aggregates can also be used with implicit grouping by selecting other columns to be included in the result. In the example below, the
order
column is included in the new data table, so the calculated column quantity
is broken out by order
....
table = table.select("order").sum("quantity").table();
Calculated columns can also be created using custom formulas using the
calc
method.In the example below, a new column,
more_quantity
, is calculated as the sum
of the quantity
column times 10
and returned in a new table. This is done using a custom function and a delegate row
. The function will also have the index position and all rows in the data table available as it is being evaluated....
table = table.select()
.column("more_quantity")
.sum().calc((row, index, rows) => {
return row["quantity"] * 10;
}) .table();
Last modified 2yr ago