CODEWITHSUNDEEP
kdb+
BlackPearl
BlackPearl

Reputation: 115

kdb+ tests to check we're not flipping back and forth

How do we find out if the sym is flipping back and forth or not? Once the syms changes to some other sym, it should not go back to previously occurred symbols in history. Everytime the symbols flips/changes to new symbols, then new symbol should be unique and should not occurred in previous sym.

Things we want here is:

  1. We want to have a Boolean column 1b if the rollover happened
  2. We want to have another Boolean column 1b is rollover happned and the sym has flipped back and forth. (the current sym has occurred in previous or future date syms.)
//To create a sample table
tmp:([]sdate:`date$();sym:`symbol$();name:`symbol$());
{`tmp insert (x;`VXV2;`someName1)} each tdate;
{`tmp insert (x;`VXJ2;`someName2)} each tdate;
{`tmp insert (x;`VXG8;`someName3)} each tdate;
{`tmp insert (x;`VXZ4;`someName4)} each tdate;

//update the dataset to get the scenario
tmp:`sdate`sym xasc tmp;
tmp:update sym:`VXG8 from tmp where sdate=2020.04.11;
tmp:update sym:`VXN6 from tmp where sdate>=2020.04.21;

//the below query is we are interested in to find the rollover and sym flipping back and forth.
select from tmp where name=`someName1

//notes
from 2020-03-30 till 2020-04-10 then sym is VXV2
from 2020-04-11 till 2020-04-11 then sym is VXG8  // the sym was updated to VXG8  
from 2020-04-12 till 2020-04-20 then sym is VXV2  // the sym was flipped back to VXV2 from VXG8
from 2020-04-21 till 2021-03-29 then sym is VXN6  // the sym was updated to VXN6

enter image description here

Upvotes: 0

Views: 158

Answers (2)

terrylynch
terrylynch

Reputation: 13657

It depends on exactly how you want the booleans to look - only one true boolean on the flip or all true booleans for every duplicate sym - but this does the former:

update roll:differ sym,dup:differ[sym]&not i=group[sym][;0]sym from select from tmp where name=`someName1

Similar to Cathans answer

Upvotes: 2

Cathan Gormley
Cathan Gormley

Reputation: 61

You can use differ on a list to return where it changes. For your second column, you can also use differ and filter out elements that are the first occurrence in the list.

q)list:`A`A`A`A`B`A`A`A`C`C
q)differ list
1000110010b
q)differ[list] and not @[count[list]#0b; list?distinct list; :; 1b]
0000010000b

Upvotes: 4

Related Questions