0

I I have a simple table in KDB and would like to know if there's an elegant way to update the first N rows and set all the values to 0n without explicitly specifying each of the column names because I do not know how many columns I have a-priori.

tbl:([] c1:1+til 10; c2:1+til 10; c3:1+til 10)

I can select the top N rows using select [10] from t....but I'm not very sure how to update the top N row.

Any help is much appreciated.

edit

Saw the reply from @Jorge Sawyer but didn't really understand what the code was doing enough to modify it myself. Hence require help to do the same for the following date-keyed table:

tbl2:([date:2021.01.01+til 10] c1:1.1+til 10; c2:1.1+til 10; c3:1.1+til 10)

JKuek
  • 11
  • 2

2 Answers2

1

nullTopN will hit a type error because tbl2 now has dates and floats. I would use prev to get the null values of the appropriate type rather than over-engineering with casting the nulls.

f:{[tbl;n] 
     //[table;where;by;cols]
     ![tbl;enlist(<;`i;n);0b;
         cols[tbl]!{[n;c] (#;n;(prev;(enlist;(first;c))))}[n] each cols[tbl]] }

/
This is equivalent to the below update query but for all columns by creating the dynamic functional update above:

update n#prev enlist first col1 from tbl where i < n
\

q)f[tbl2;3]
date      | c1   c2   c3
----------| --------------
          |
          |
          |
2021.01.04| 4.1  4.1  4.1
2021.01.05| 5.1  5.1  5.1
2021.01.06| 6.1  6.1  6.1

More info on functional update see:

https://code.kx.com/q/basics/funsql/

https://code.kx.com/q/ref/next/#prev

Matt Moore
  • 2,705
  • 6
  • 13
0

You can use something like this:

q)tbl:([] c1:1+til 10; c2:1+til 10; c3:1+til 10);
q)nullTopN:{[tbl;n]![tbl;enlist(<;`i;n);0b;{x!count[x]#0N}cols[tbl]]};
q)nullTopN[tbl;3]
c1 c2 c3
--------



4  4  4
5  5  5
6  6  6
7  7  7
8  8  8
9  9  9
10 10 10
Jorge Sawyer
  • 1,331
  • 4
  • 7
  • Hi Jorge. Much appreciated! I modified the table slightly in my original post and was wondering if you could help with that. Many thanks! – JKuek Feb 24 '21 at 07:28
  • Ah, the issue there is that your second table doesn't have columns of al the same type. I fully agree with Matt Moore in his answer that use of `prev` is better than trying to cleverly account for different types (which will especially be difficult if you have compound columns, e.g. strings). – Jorge Sawyer Feb 25 '21 at 13:55