cancel
Showing results for 
Search instead for 
Did you mean: 

question of asof join

kc3031
New Contributor
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[`sector`datetime;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[
    `datetime`sector;
    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 REPLIES 3

sfrazer660
New Contributor
Hi Kelly,

Quick observation before the weekend, but should tmp:aj[`datetime`sector...] be the other way round? ie (tmp:aj[`sector`datetime...]). 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[`sector`datetime;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[
    `datetime`sector;
    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

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

kc3031
New Contributor
My problem was caused by a stupid typo in sector... 

Actually asof join works well here.

Thanks for Jack's help!