1

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 append from "info" the shares and divisor values for each ticker based on the date. How can I achieve this? Below 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
Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
Fomalhaut -C
  • 320
  • 2
  • 13
  • 1
    It's much easier for people to test & provide answers if you supply an example that is "Minimal, Complete, and Verifiable". See here: https://stackoverflow.com/help/mcve In short, it'd be very helpful if you could provide Q expressions to generate your input tables, e.g. with real timestamps not `ts0` etc – Jonathon McMurray Feb 05 '18 at 17:25
  • Assume tables cannot be read in a different way or are from some data store – Fomalhaut -C Feb 05 '18 at 17:30
  • 1
    That's better, but it's much more useful if you can provide a Q expression to set up the tables. e.g. ``data:([] dt:(10?2017.01.01+til 2)+10?.z.t;sym:10?`AAPL`GOOG;bid:100+10?5;ask:105+10?5);info:([] date:2017.01.01 2017.01.01 2017.01.02;sym:`AAPL`GOOG`AAPL;shares:500 100 200;divisor:2 1 2)`` – Jonathon McMurray Feb 05 '18 at 17:33

3 Answers3

1

If matching based on an exact date match then you can use lj. For this to work you will need to create a date column in the data table and key info by date and sym. Like so:

(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 delete the date column from this output.

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
1

It might be useful for you to use the stepped attribute [ http://code.kx.com/q/cookbook/temporal-data/#stepped-attribute ]

This will allow you to have e.g. missing dates from the info table and use the "most recent" date instead (so you don't have to have data for every sym every day). For example, without stepped attribute:

q)data:([] dt:(10?2017.01.01+til 2)+10?.z.t;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

Note the nulls for GOOG on 2017.01.02. With stepped 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 as there is no new value on 2017.01.02

Jonathon McMurray
  • 2,881
  • 1
  • 10
  • 22
1

Could possibly use an aj as well.

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
AquaQRoss
  • 11
  • 1