I have a table in kdb with 3 columns: date, currency, fx rate. Some of the fx are missing, and I want to replace the null fx by the value on the previous day, for the same currency obviously. I cannot use fills as it is because it may mix currencies. I tried fills with variations of group by, but it doesn't work. Do you know how to do this? This is my incorrect query:
update fills fx from (`ccy`date xasc t)
And this is the original table (made up data):
date ccy fx
2017.08.01 AUS 0.57
2017.08.01 CAN 0.61
2017.08.01 EUR 0.91
2017.08.01 USD 0n
2017.08.02 AUS 0n
2017.08.02 CAN 0.62
2017.08.02 EUR 0n
2017.08.02 USD 0n
2017.08.03 AUS 0n
2017.08.03 CAN 0n
2017.08.03 EUR 0n
2017.08.03 USD 0n
2017.08.04 AUS 0.57
2017.08.04 CAN 0.62
2017.08.04 EUR 0.91
2017.08.04 USD 0.78
2017.08.05 AUS 0.59
2017.08.05 CAN 0.61
2017.08.05 EUR 0.92
2017.08.05 USD 0.79
2017.08.06 AUS 0.58
2017.08.06 CAN 0.62
2017.08.06 EUR 0.91
2017.08.06 USD 0.77
You can use update - by:
update fills fx by ccy from t