Keyed Tables and Key-Based Lookups

Question

A trading system maintains reference data for financial instruments. Each instrument is uniquely identified by its symbol and exchange.

Given the following keyed table:

t:([sym:`AAPL`MSFT`GOOG;exchange:`NASDAQ`NASDAQ`NASDAQ]
    sector:`Technology`Technology`Technology;
    lotSize:100 100 100)

1. What is a keyed table?

2. Why might a keyed table be useful in a real-world application?

3. Demonstrate how to perform:

   • A lookup using a single key
   • A lookup using multiple keys

Solution

Single Key Lookup

t[(`AAPL;`NASDAQ)]

Result:

sector | `Technology
lotSize| 100

Multiple Key Lookup

Index with a table of keys to get the matching value rows:

t[([] sym:`AAPL`GOOG; exchange:`NASDAQ`NASDAQ)]

Result:

sector     lotSize
------------------
Technology 100
Technology 100

An equivalent form is:

t[((`AAPL;`NASDAQ);(`GOOG;`NASDAQ))]

To keep the key columns in the result, use Take (#) with the table of keys instead:

([] sym:`AAPL`GOOG; exchange:`NASDAQ`NASDAQ)#t

sym  exchange| sector     lotSize
-------------| ------------------
AAPL NASDAQ  | Technology 100
GOOG NASDAQ  | Technology 100

Explanation

What is a Keyed Table?

A keyed table is a dictionary whose keys and values are both tables.

For example:

([id:1 2 3] price:100 200 300)

is equivalent to:

([] id:1 2 3)!([] price:100 200 300)

The key table is:

id
--
1
2
3

and the value table is:

price
-----
100
200
300

Internally, a keyed table is therefore a dictionary of tables.

You can see this by applying:

0N!([id:1 2 3] price:100 200 300)

which prints the underlying structure — a flipped dictionary (+) keyed (!) to another flipped dictionary:

(+(,`id)!,1 2 3)!+(,`price)!,100 200 300

Why Are Keyed Tables Useful?

Keyed tables are commonly used for reference data.

Examples include:

Suppose a trading application needs to repeatedly look up information about a symbol.

With a keyed table the lookup reads like a dictionary lookup:

t[(`AAPL;`NASDAQ)]

rather than a query such as select from t where sym=`AAPL, exchange=`NASDAQ. Note that a plain keyed-table lookup still searches the key column(s); for large reference tables with a single key column, applying the unique attribute (`u#) to that column lets q use a hash lookup instead.

Single Key Lookup

The key columns of t are sym and exchange, so the lookup key is:

(`AAPL;`NASDAQ)

Applying:

t[(`AAPL;`NASDAQ)]

returns the matching row from the value table:

sector | `Technology
lotSize| 100

Multiple Key Lookup

Multiple rows can be retrieved simultaneously.

One approach is to provide a key table:

([] sym:`AAPL`GOOG;
    exchange:`NASDAQ`NASDAQ)

and use it to index the keyed table:

t[([] sym:`AAPL`GOOG;
      exchange:`NASDAQ`NASDAQ)]

Result:

sector     lotSize
------------------
Technology 100
Technology 100

An equivalent tuple-based syntax is:

t[((`AAPL;`NASDAQ);(`GOOG;`NASDAQ))]

although the key-table form is usually easier to read and scales better when many keys are involved.

Summary

A keyed table is a dictionary whose keys and values are tables of equal length.

They are commonly used for:

• Reference data
• Instrument metadata
• Account information
• Order state
• Configuration data

Because keyed tables support direct key-based access, they provide a convenient way to retrieve rows by key.