I’m doing a project on some price data and I’m looking to use aj to fill in days when I’m missing an average price with the price when last traded.
I use the same table twice in aj to try and fill the blanks. However, it doesn’t fill as I would have thought it would.
Could you advise where I’m going wrong in the simplified example below?
Simon
tt:([]t:1998.01.02 1998.01.03 1998.01.04 1998.01.05 1998.01.06 1998.01.07 1998.01.09;
r:`COSC.SI`COSC.SI`COSC.SI`COSC.SI`COSC.SI`COSC.SI`COSC.SI;
p:(0.9;0n;0n;0n;0.8;0.7473684;0.7195));
qt:([]t:1998.01.02 1998.01.03 1998.01.04 1998.01.05 1998.01.06 1998.01.07 1998.01.09;
r:`COSC.SI`COSC.SI`COSC.SI`COSC.SI`COSC.SI`COSC.SI`COSC.SI;
q:(0.9;0n;0n;0n;0.8;0.7473684;0.7195));
aj[`r`t;tt;qt]
got:
t r p q
--------------------------------------
1998.01.02 COSC.SI 0.9 0.9
1998.01.03 COSC.SI
1998.01.04 COSC.SI
1998.01.05 COSC.SI
1998.01.06 COSC.SI 0.8 0.8
1998.01.07 COSC.SI 0.7473684 0.7473684
1998.01.09 COSC.SI 0.7195 0.7195
expected:
t r p q
--------------------------------------
1998.01.02 COSC.SI 0.9 0.9
1998.01.03 COSC.SI 0.9 0.9
1998.01.04 COSC.SI 0.9 0.9
1998.01.05 COSC.SI 0.9 0.9
1998.01.06 COSC.SI 0.8 0.8
1998.01.07 COSC.SI 0.7473684 0.7473684
1998.01.09 COSC.SI 0.7195 0.7195
You should use fills operator to replace nulls with most recent values. Statement
fills tt
will fill all columns. To specify columns explicitly use:
update fills p from tt
Separately. aj doesn't work in way you expect, because nulls are not removed from qt table. Hence, null q values are joined to null p values. If you filter nulls, you'll get result you expect:
aj[`r`t;tt;delete from qt where null q]
But this is not the best way of filling gaps with most recent values