Array access

The following methods provide array access to columns in a table, and create a row index to locate specific values.

Create an array

A column name followed by an underscore represents a column as an array of all values within the column. For example, Sym_ creates an array of all the tables's Symbols:

t.update("SA=Sym_")

Create an Index

You may want to narrow your results and ask for the element in a certain row. In that case, you should create a row index for your table:

t.update("Index=i")// a positional row index, as an Integer

t.update("Index=ii") // a positional row index, as a Long

The variables i and ii both represent row numbers. However, Integers are limited to values up to 231-1, while Longs can represent values up to 263-1. In other words, to avoid precision problems you should use the ii variable, unless you specifically need an Integer to pass to another function. Using the i variable in a table with more than 2 billion rows will result in an error.

Within a query, square bracket operators get an element from the array based on the row index. For example, [ii-1] indicates a relative position of one row back from the index.

A third variable, k, provides a unique reference for a particular row in the table:

t.update("Index=k")// the internal index within the table

The internal index is not stable between operations and values are not necessarily contiguous. As with the i and ii row indexes, the k index is unreliable for live or intraday data due to query engine operations.

To use this method properly within a query, you must know the index key of a row.

Use the Index in a query

The row index allows users to pinpoint certain values within an array. Within a query, square bracket operators get an element from the array based on the row index. For example, Sym_ creates an array, and Sym_[ii-1] uses the index to go one row back, or get the Symbol value in the previous row. Sym_[ii+3] creates an array, and then gets the Symbol value three rows ahead of the current row:

t.update("X=Sym_[ii-1]")

t.update("Y=Sym_[ii+3]")

Let's take a look at an example query that combines these methods:

t=t.update("Difference = (Sym=Sym_[ii-1]) ? Price-Price_[ii-1] : null")

This asks for the difference in price in Symbol values using a ternary operator, which returns results based upon a true or false comparison. We want to compare the same Symbol, so the first clause in the formula checks if the value of the Sym in the current row equals the value in the previous row. Then, we want to determine the current price minus the price in the previous row. If the Symbol is not the same in that row (a false comparison), we get a "null" value. If the comparison is true, the formula will return the difference in price.

Examples

t = db.t("LearnDeephaven", "StockTrades").where("Date = `2017-08-21`").headBy(3, "Sym")
t2 = t.update("SA=Sym_")

t3 = t.update("Index=i")
t4 = t.update("Index=ii")

t5 = t.update("X=Sym_[ii-1]")
t6 = t.update("Y=Sym_[ii+3]")

t7 = t.update("Index=k")

t8 = t.update("Difference = (Sym=Sym_[ii-1]) ? Last-Last_[ii-1] : null")

t9 = db.t(“LearnDeephaven”, “StockTrades”).where(“Date = `2017-08-21`“).update(“Index=k”).headBy(100, “Exchange”).sort(“Index”)