CODEWITHSUNDEEP
kdb+
JKuek
JKuek

Reputation: 11

(KDB/Q+) Update top N rows of a simple table

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)

Upvotes: 0

Views: 1058

Answers (2)

Matt Moore
Matt Moore

Reputation: 2800

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

Upvotes: 1

Jorge Sawyer
Jorge Sawyer

Reputation: 1341

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

Upvotes: 0

Related Questions