CODEWITHSUNDEEP
kdb+
howard
howard

Reputation: 23

Populate null value with preceding values for multiple columns without specifying column names

I have a table containing 30+ columns, each of which has quite a few null values for records. To fill null values with preceding values, I use fills function and specify each column name in update statement as follows at the moment. (The below example has 4 columns only for the purpose of explanation)

      trade:([]date:raze 3#enlist .z.d+til 10; ccy:30#`EUR`JPY`AUD; fx:30?(1.0 0.2 0.4 0n); mult:30?(10.1 0n 30.3 0n));
      update fills fx, fills mult by ccy from trade;

When it comes to a lot more columns, it gets cumbersome to specify each column name in the same way (fills col1, fills col2, fills col3...). Don't we have a way to fill the null values for all the columns of a table more easily?

Thank you very much for your help in advance!

Upvotes: 1

Views: 1422

Answers (2)

Jonathon McMurray
Jonathon McMurray

Reputation: 2991

You can actually use fills on the whole table e.g.

q)fills trade
date       ccy fx  mult
-----------------------
2020.10.26 EUR 1   10.1
2020.10.27 JPY 1   10.1
2020.10.28 AUD 0.2 30.3
2020.10.29 EUR 0.4 30.3
2020.10.30 JPY 0.2 30.3
2020.10.31 AUD 0.4 30.3
2020.11.01 EUR 0.4 30.3
..
q)sum each null each flip fills trade /check there are no nulls
date| 0
ccy | 0
fx  | 0
mult| 0

Upvotes: 3

Cathal O'Neill
Cathal O'Neill

Reputation: 3179

You could try this functional update

![trade;();(enlist`ccy)!enlist`ccy;{x!fills,'x}cols trade]

This will forward fill all columns in trade. To be more specific about which columns you want to forward fill, you could save these columns as a variable and use them like so

q)cls:`fx`mult
q)![trade;();(enlist`ccy)!enlist`ccy;{x!fills,'x}cls]
date       ccy fx  mult
-----------------------
2020.10.25 EUR 1   10.1
2020.10.26 JPY 1   10.1
2020.10.27 AUD 0.2 30.3
2020.10.28 EUR 0.4 10.1
2020.10.29 JPY 0.2 30.3
..

Upvotes: 3

Related Questions