q kdb fill missing fxrates

Question

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

Gurgitation

Answer 1

You can use update - by:

update fills fx by ccy from t

Katlaps

Answer 2

Very close - all you need to do is add "by":

t:([]date:raze 3#enlist .z.d+til 100;ccy:300#`EUR`CAN`AUS;fx:300?(1.0 0.2 0.4 0n))

    10#t
    date       ccy fx 
    ------------------
    2017.08.16 EUR 1  
    2017.08.17 CAN 0.4
    2017.08.18 AUS    
    2017.08.19 EUR 0.4
    2017.08.20 CAN 0.2
    2017.08.21 AUS 1  
    2017.08.22 EUR 1  
    2017.08.23 CAN    
    2017.08.24 AUS 1  
    2017.08.25 EUR 1  

    10# update fills fx by ccy from t 
    date       ccy fx 
    ------------------
    2017.08.16 EUR 1  
    2017.08.17 CAN 0.4
    2017.08.18 AUS    
    2017.08.19 EUR 0.4
    2017.08.20 CAN 0.2
    2017.08.21 AUS 1  
    2017.08.22 EUR 1  
    2017.08.23 CAN 0.2
    2017.08.24 AUS 1  
    2017.08.25 EUR 1  

Curettage