CODEWITHSUNDEEP
kdb+
Victor Gl
Victor Gl

Reputation: 91

Remove table duplicates under certain conditions

I have a table like below that shows me some pnl by instrument (code) for some shifts, maturity, etc.

Instrument 123 appears two times (2 sets of shift, booknumber, insmat but different pnl). I would like to clean the table to only keep the first set (3 first rows).

> code | shift | pnl | booknumber | insmat
  123    -20%    5     1234         2021.01.29
  123    -0%     7     1234         2021.01.29
  123    +20%    9     1234         2021.01.29
  123    -20%    4     1234         2021.01.29
  123    -0%     6     1234         2021.01.29
  123    +20%    8     1234         2021.01.29
  456    -20%    1     1234         2021.01.29
  456    -0%     2     1234         2021.01.29
  456    +20%    3     1234         2021.01.29

If there were no shifts involved I would do something like this:

select first code, first pnl, first booknumber, first insmat by code from t

Would love to hear if you have a solution! Thanks!

Upvotes: 0

Views: 92

Answers (1)

Cathal O'Neill
Cathal O'Neill

Reputation: 3179

If the shift pattern is consistently 3 shifts, you could use

q)select from t where 0=i mod 3
code shift pnl booknumber insmat
------------------------------------
123  20    5   1234       2021.01.29
123  20    4   1234       2021.01.29
456  -20   1   1234       2021.01.29

Alternative solution with an fby

q)select from t where shift=(first;shift)fby code
code shift pnl booknumber insmat
------------------------------------
123  20    5   1234       2021.01.29
123  20    4   1234       2021.01.29
456  -20   1   1234       2021.01.29

This will only work if the first shift value is unique within the shift pattern however.

Upvotes: 1

Related Questions