CODEWITHSUNDEEP
kdb+q-lang
chrise
chrise

Reputation: 4253

kdb voolkup. get value from table that is mapped to smallest val larger than x

Assuming I have a dict

d:flip(100 200 400 800 1600; 1 3 4 6 10)

how can I create a lookup function that returns the value of the smallest key that is larger than x? Given a table 

tbl:flip `sym`val!(`a`b`c`d; 50 280 1200 1800)

I would like to do something like 

{[x] : update new:fun[x[`val]] from x} each tbl

to end up at a table like this

tbl:flip `sym`val`new!(`a`b`c`d; 50 280 1200 1800; 1 4 10 0N)

sym val new
a   50    1
b   280   4
c   1200  10
d   1800

Upvotes: 1

Views: 560

Answers (2)

Thomas Smyth
Thomas Smyth

Reputation: 5644

I think you will want to use binr to return the next element greater than or equal to x. Note that you should use a sorted list for this to work correctly. For the examples above, converting d to a dictionary with d:(!). flip d I came up with:

q)k:asc key d
q)d k k binr tbl`val
1 4 10 0N
q)update new:d k k binr val from tbl
sym val  new
------------
a   50   1
b   280  4
c   1200 10
d   1800

Where you get the dictionary keys to use with: k k binr tbl`val.

Edit: if the value in the table needs to be mapped to a value greater than x but not equal to, you could try:

q)show tbl:update val:100 from tbl where i=0
sym val
--------
a   100
b   280
c   1200
d   1800
q)update new:d k (k-1) binr val from tbl
sym val  new
------------
a   100  3
b   280  4
c   1200 10
d   1800

Upvotes: 1

effbiae
effbiae

Reputation: 1157

stepped dictionaries may help http://code.kx.com/q/cookbook/temporal-data/#stepped-attribute

q)d:`s#-0W 100 200 400 800 1600!1 3 4 6 10 0N
q)d 50 280 1200 1800
1 4 10 0N

Upvotes: 5

Related Questions