Step Dictionary Table Update

Question

An exchange defines tick sizes using the following price bands:

You are given a table containing instrument prices:

prices:([]sym:`AAPL`MSFT`GOOG`TSLA`NVDA;
          px:0.75 5 12 63 150)

Result:

sym  px
---------
AAPL 0.75
MSFT 5
GOOG 12
TSLA 63
NVDA 150

Add a new column called tickSize containing the appropriate tick size for each price.

Expected result:

sym  px   tickSize
------------------
AAPL 0.75 0.01
MSFT 5    0.05
GOOG 12   0.1
TSLA 63   0.1
NVDA 150  0.5

Solution

update tickSize:(`s#0 1 10 100f!0.01 0.05 0.10 0.50)[px] from prices

Result:

sym  px   tickSize
------------------
AAPL 0.75 0.01
MSFT 5    0.05
GOOG 12   0.1
TSLA 63   0.1
NVDA 150  0.5

Alternative Solution

Using an as-of join:

rules:([]px:0 1 10 100f; tickSize:0.01 0.05 0.10 0.50)

aj[`px;prices;rules]

Result:

sym  px   tickSize
------------------
AAPL 0.75 0.01
MSFT 5    0.05
GOOG 12   0.1
TSLA 63   0.1
NVDA 150  0.5

Alternative Solution: Left Join With a Step Keyed Table

The same rules can be held as a keyed table with the sorted attribute applied to the whole table. A left join (lj) against it then behaves as a step lookup:

stepKeyedTable:`s#([px:0 1 10 100f]; tickSize:0.01 0.05 0.10 0.50)
prices lj stepKeyedTable

Same result.

Explanation

Note the f on the band boundaries (0 1 10 100f). Because px contains 0.75, the whole column is float, and the lookup keys must have the same type: with long keys (0 1 10 100) the lookup fails with 'type.

The step dictionary's lookup rule — largest key ≤ the lookup value — is applied to every px in one pass. 0.75 and 5 fall back to keys 0 and 1; 12 and 63 both fall back to key 10; 150 falls back to key 100.

The aj alternative gets the same result by as-of joining prices against a rules table keyed on the band boundaries — useful if the rules already live in a table, but the step dictionary is more concise for a one-off threshold lookup like this.