question of asof join

Community Support
1

Hello experts,

I am trying to use asof join to join the stock return, sector return and market return.

My table looks as follows:

stock (TABLE):

stock | datetime                           | stockReturn | sector

AAPL  2017.04.03T09:30:00.00       0.00151         TEC

sector_market (TABLE):

sector | datetime                          | sectorReturn | marketReturn

TEC      2017.04.03T09:30:00.00     0.00187          0.00142

I use the following query to join the table.

aj[sectordatetime;s_s;sector_market]

I would love to get back in the following format:

stock | datetime | stockReturn | sector | sectorReturn | marketReturn

Let’s track the joined time and sector. The result shows that time is matched while sector is not matched!

tmp:aj[

    datetimesector;

    stock;

    update TimeMarket:datetime, SectorMarket:sector from sector_market

]

It gives me the wrong answer, for example:

stock | datetime                        | stockReturn | sector | sectorReturn | marketReturn | TimeMarket                   | SectorMarket

AAPL 2017.04.03T09:30:00.00    0.00151        TEC       0.00187         0.00142           2017.04.03T09:30:00.00  FIN

and finally, if merged, it gives me the FIN as a wrong sector.

Can anyone helps on the problem?

Many thanks in advance!

Best,

Kelly

2

Hi Kelly,

Quick observation before the weekend, but should tmp:aj[datetimesector…] be the other way round? ie (tmp:aj[sectordatetime…]). I assume you are trying to match exactly on sector and the asof part should be datetime.


Hope this helps,

Simon

On Friday, June 16, 2017 at 2:27:45 PM UTC+1, kc3…@columbia.edu wrote:

Hello experts,

I am trying to use asof join to join the stock return, sector return and market return.

My table looks as follows:

stock (TABLE):

stock | datetime                           | stockReturn | sector

AAPL  2017.04.03T09:30:00.00       0.00151         TEC

sector_market (TABLE):

sector | datetime                          | sectorReturn | marketReturn

TEC      2017.04.03T09:30:00.00     0.00187          0.00142

I use the following query to join the table.

aj[sectordatetime;s_s;sector_market]

I would love to get back in the following format:

stock | datetime | stockReturn | sector | sectorReturn | marketReturn

Let’s track the joined time and sector. The result shows that time is matched while sector is not matched!

tmp:aj[

    datetimesector;

    stock;

    update TimeMarket:datetime, SectorMarket:sector from sector_market

]

It gives me the wrong answer, for example:

stock | datetime                        | stockReturn | sector | sectorReturn | marketReturn | TimeMarket                   | SectorMarket

AAPL 2017.04.03T09:30:00.00    0.00151        TEC       0.00187         0.00142           2017.04.03T09:30:00.00  FIN

and finally, if merged, it gives me the FIN as a wrong sector.

Can anyone helps on the problem?

Many thanks in advance!

Best,

Kelly

3

Hi Simon,

Thanks for your reply.

I’m trying to match exactly on datetime as well as sector. Suppose at different timestamps, each stock should have corresponding stock return, sector return and market return. Well, aj seems to work only on numeric data but not characters like sector name… I guess that cause my join wrong. However, I don’t have any idea to solve this problem…

Best,

Kelly

4

My problem was caused by a stupid typo in sector… 

Actually asof join works well here.

Thanks for Jack’s help!