Adding a column based on other columns KDB+

Question

I have a table ("ibmqt") with a number of columns, and I would like to add a new column, containing boolean values indicating for each row whether one column ("bid") is greater than or equal to another column ("ask").

My most successful attempt so far is this:

ibmqt: update (pricecross:select bid>=ask from ibmqt) from ibmqt

However, this results in the following:

time         sym bid   ask   bsize asize pricecross
----------------------------------------------------
00:00:59.063 IBM 43.53 43.57 10000 9000  (,`ask)!,0b
00:01:03.070 IBM 43.54 43.59 6500  3000  (,`ask)!,0b
00:02:31.911 IBM 43.56 43.6  500   4500  (,`ask)!,0b
00:03:43.070 IBM 43.56 43.56 10000 2500  (,`ask)!,1b
00:06:01.170 IBM 43.54 43.56 8500  4500  (,`ask)!,0b
00:06:11.081 IBM 43.56 43.58 500   1500  (,`ask)!,0b
00:08:15.126 IBM 43.55 43.57 1500  9000  (,`ask)!,0b

Obviously in the "pricecross" column I just want 0, 0, 0, 1, 0 etc.

Any suggestions?

Answer 1

There is no need for a nested select. This will do what you need:

ibmqt:update pricecross:bid>=ask from ibmqt

Or you can update ibmqt in place:

update pricecross:bid>=ask from `ibmqt

q is an array language, therefore bid>=ask compares two columns pairwise and returns a list of booleans. This will illustrate the idea:

1 2 3 >= 0 2 4 / 110b

The list of booleans is then assigned to a new column pricecross.

Answer 2

since >= is overloaded to work with both atom and list, using pricecross:bid>=ask is the best solution here :

q)update pricecross:bid>=ask from ibmqt 

But there is a slightly different way of getting the same results :

q)update pricecross:bid>='ask from ibmqt 
q)update pricecross:>='[bid;ask] from ibmqt 

This is particularly useful when the dyadic function works only with atoms :

q)update pricetolerance:tolerance'[bid;ask] from ibmqt