0

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!

howard
  • 13
  • 2

2 Answers2

2

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
..
Cathal O'Neill
  • 2,522
  • 1
  • 6
  • 17
  • Very optimal way! I have been avoiding functional select/update as the readability does not seem so great so far, but I should def try! Using parse function seem to be one way, but I will need to find a good source to revisit the syntax to shed some light on it – howard Oct 25 '20 at 11:41
  • 1
    There's a great kx whitepaper on functional form here https://code.kx.com/q/wp/parse-trees/ – Cathal O'Neill Oct 25 '20 at 11:56
  • This looks very informative and useful! Will read it and try to get the knack of it – howard Oct 25 '20 at 12:24
2

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
Jonathon McMurray
  • 2,881
  • 1
  • 10
  • 22
  • Thanks @jonathon. Applying `fills` to an entire table seems good to use when you would like to fill all the fields without grouping. – howard Oct 31 '20 at 01:41