kdb

how to use amend in update statement


We have a table tab

tab:update volume:0n from ([] date:2024.01.01+til 10;sym:10?`appl`msgt`googl;volume:10#(enlist 0.5+300?til 100)) where i in (1;5;8);

enter image description here

We want to update the column volume such a way that, copy bucket value of index 299 to 200 and make zero buckets 201 to 299.

This we can do using amend like this, but amend throws type error when volume is empty/null.

In this behavior we want to not throw error but return empty/null as it is and process only when there is something in volume column.

//original value
first exec volume from select from tab where i=0

enter image description here

//updated expected values, works where volume is not null else gives type error
first exec volume from {[start;end;tab] tt:.[tab;(::;`volume;200);:;.[tab;(::;`volume;299)]]; .[tt;(::;`volume;start+til end-start);:;0f]}[201;299;select from tab where i=0]

enter image description here

But this gives type error where volume is null when we pass the whole table

first exec volume from {[start;end;tab] tt:.[tab;(::;`volume;200);:;.[tab;(::;`volume;299)]]; .[tt;(::;`volume;start+til end-start);:;0f]}[201;299;select from tab]

What's the best way we can check if volume is null then return null/empty do anything else do the processing something like this

update volume:{ $[count x;"do processing here using amend"; x] }'[volume] from tab

Solution

  • Switching start & end variables for y & z respectively.

    q)update volume:{$[1=count x;x;@[;y+til z-y;:;0f]@[x;200;:;x 299]]}'[volume;201;299]from tab
    

    @ amend is sufficient here as the lambda is operating over each volume row and so there is no need to index at depth.