join portfolio table to latest prices

[Joe L]

Hi,

How can I join two tables? I have the following tables and I would like to pull the latest values into the portfolio and total them.


q)latestPrices:([] sym:`A`B`C`D; price:10 11 9 8.)
q)latestPrices
sym price
---------
A 10
B 11
C 9
D 8
q)portfolioJohn:([] ticker:`C`D`E; position:100 200 10)
q)portfolioJohn
ticker position
---------------
C 100
D 200
E 10


[Ryan Hamilton]

Hi Joe,

Use kdb left join lj to make sure entries that do not have the latest price also get pulled in like so:

q)(`sym xcol portfolioJohn) lj 1!latestPrices
sym position price
——————
C 100 9
D 200 8
E 10

Kdb joins depend on column names being the same, here on the left hand side I rename ticker to sym. On the right hand side of lj the table should be keyed by it’s key joining column so I use the shortcut 1! to make it keyed by the first column.

See kdb qsql joins for more information. Though you may also want to look into aj/qj for asof time joins.

– Ryan

[Author]

Posts