How to match date and row from 2 lists (KDB)?

Question

I have two lists:

data: dt sym bid ask 2017.01.01D05:00:09.140745000 AAPL 101.20 101.30 2017.01.01D05:00:09.284281800 GOOG 801.00 802.00 2017.01.02D05:00:09.824847299 AAPL 101.30 101.40 info: date sym shares divisor 2017.01.01 AAPL 500 2 2017.01.01 GOOG 100 1 2017.01.02 AAPL 200 2 

I need to add from "info" the values ​​of stocks and dividers for each ticker depending on the date. How can i achieve this? The following is an example:

 result: dt sym bid ask shares divisor 2017.01.01D05:00:09.140745000 AAPL 101.20 101.30 500 2 2017.01.01D05:00:09.284281800 GOOG 801.00 802.00 100 1 2017.01.02D05:00:09.824847299 AAPL 101.30 101.40 200 2 

Answer 1

If the match matches the exact date match, you can use lj . To do this, you will need to create a date column in the data table and the info key on date and sym . For instance:

 (update date:`date$dt from data)lj 2!info dt sym price date shares divisor --------------------------------------------------------------------- 2018.02.04D17:25:06.658216000 AAPL 103.9275 2018.02.04 500 2 2018.02.04D17:25:06.658216000 GOOG 105.1709 2018.02.04 100 1 2018.02.05D17:25:06.658217000 AAPL 105.1598 2018.02.05 200 2 2018.02.05D17:25:06.658217000 GOOG 104.0666 2018.02.05 

You can then remove the date column from this output.

Answer 2

You may find it helpful to use the step attribute [ http://code.kx.com/q/cookbook/temporal-data/#stepped-attribute ]

This will allow you to have, for example, missing dates from the info table and instead use the "most recent" date (so you do not need to have data for each character every day). For example, without a step attribute:

 q)data:([] dt:(10?2017.01.01+til 2)+10?.zt;sym:10?`AAPL`GOOG;bid:100+10?5;ask:105+10?5) q)info:([] date:2017.01.01 2017.01.01 2017.01.02;sym:`AAPL`GOOG`AAPL;shares:500 100 200;divisor:2 1 2) q)(update date:`date$dt from data) lj 2!info dt sym bid ask date shares divisor -------------------------------------------------------------------- 2017.01.01D04:04:03.440000000 GOOG 104 105 2017.01.01 100 1 2017.01.01D14:00:02.748000000 GOOG 104 105 2017.01.01 100 1 2017.01.02D09:34:52.869000000 GOOG 102 106 2017.01.02 2017.01.02D16:44:16.648000000 AAPL 100 107 2017.01.02 200 2 2017.01.01D08:48:23.285000000 AAPL 102 108 2017.01.01 500 2 2017.01.02D02:31:11.038000000 AAPL 104 109 2017.01.02 200 2 2017.01.01D05:50:50.463000000 GOOG 104 109 2017.01.01 100 1 2017.01.02D02:13:45.275000000 AAPL 101 107 2017.01.02 200 2 2017.01.01D10:25:30.322000000 AAPL 104 109 2017.01.01 500 2 2017.01.01D14:51:12.687000000 AAPL 103 109 2017.01.01 500 2 

Pay attention to the zeros for GOOG on 2017.01.02 . With step attribute:

 q)(update date:`date$dt from data) lj `s#2!`sym xasc `sym`date xcols info dt sym bid ask date shares divisor -------------------------------------------------------------------- 2017.01.01D04:04:03.440000000 GOOG 104 105 2017.01.01 100 1 2017.01.01D14:00:02.748000000 GOOG 104 105 2017.01.01 100 1 2017.01.02D09:34:52.869000000 GOOG 102 106 2017.01.02 100 1 2017.01.02D16:44:16.648000000 AAPL 100 107 2017.01.02 200 2 2017.01.01D08:48:23.285000000 AAPL 102 108 2017.01.01 500 2 2017.01.02D02:31:11.038000000 AAPL 104 109 2017.01.02 200 2 2017.01.01D05:50:50.463000000 GOOG 104 109 2017.01.01 100 1 2017.01.02D02:13:45.275000000 AAPL 101 107 2017.01.02 200 2 2017.01.01D10:25:30.322000000 AAPL 104 109 2017.01.01 500 2 2017.01.01D14:51:12.687000000 AAPL 103 109 2017.01.01 500 2 

Here GOOG gets the values ​​for 2017.01.01 , since there is no new value for 2017.01.02

Answer 3

It is also possible to use aj.

 q)aj[`date`sym;update date:`date$dt from data;info] dt sym bid ask date shares divisor -------------------------------------------------------------------- 2017.01.02D07:57:14.764000000 GOOG 101 109 2017.01.02 200 2 2017.01.02D02:31:39.330000000 AAPL 100 105 2017.01.02 200 2 2017.01.02D04:25:17.604000000 AAPL 102 107 2017.01.02 200 2 2017.01.01D01:47:51.333000000 GOOG 104 106 2017.01.01 100 1 2017.01.02D15:50:12.140000000 AAPL 101 107 2017.01.02 200 2 2017.01.01D02:59:16.636000000 GOOG 102 106 2017.01.01 100 1 2017.01.01D14:35:31.860000000 AAPL 100 107 2017.01.01 500 2 2017.01.01D16:36:29.214000000 GOOG 101 108 2017.01.01 100 1 2017.01.01D14:01:18.498000000 GOOG 101 107 2017.01.01 100 1 2017.01.02D08:31:52.958000000 AAPL 102 109 2017.01.02 200 2