Re: sym flipping back and forth when creating continues futures contacts based on volume

[Laura]

This answer assumes your rollover and roll_rank columns are instrumental to the Expected Output and not required in it.

---

TL;DR find cumulative maxima; eliminate recurrences; upsert into an empty table; fill nulls

---

Well add two extra lines to your script: one to test the constraint described in your last comment; the other to check we find the current and not just the first maximum for the leading symbol. Well also sort tmp after the updates to ensure it reflects them. So the script ends:

 

//below steps are to generate/update sample data for testing 
// to match desired dataset which we want to create a new logic 
tmp:update volume:500.4 from tmp where sdate=2010.01.04, sym=`VXG8; 
// find current not first maximum 
tmp:update volume:600.6 from tmp where sdate=2010.01.05, sym=`VXG8; 
// confirm VXZ4 cannot recur 
tmp:update volume:700.7 from tmp where sdate=2010.01.06, sym=`VXZ4; 
tmp:`sdate xasc `volume xdesc tmp; / 
tmp:update sums roll_rank by sdate from tmp;

 

The first step enlarges Terry Lynchs answer on StackOverflow. We select the rows where the maximum changes and also mark where the symbol changes.

 

q)show q:update rollover:differ sym from select sdate,sym,name,volume from tmp where differ maxs volume 
sdate sym name volume rollover 
----------------------------------------- 
2010.01.01 VXZ4 someName4 400.4 1 
2010.01.04 VXG8 someName3 500.4 1 
2010.01.05 VXG8 someName3 600.6 0 
2010.01.06 VXZ4 someName4 700.7 1

 

The second step eliminates the last row of q, because VXZ4 may not recur.

For this we use an ancient APL idiom for finding duplicates in a vector. In APL its (??x)?x?x, which translates easily into q as (til count x)<>x?x. And we key on sdate.

 

q)show r:1!delete from q where rollover and {(til count x)<>x?x}sym 
sdate     | sym name volume rollover 
----------| ------------------------------ 
2010.01.01| VXZ4 someName4 400.4 1 
2010.01.04| VXG8 someName3 500.4 1 
2010.01.05| VXG8 someName3 600.6 0

 

This is all we need in the result. The rest is just filler.

 

q)/ make a template table 

q)s:1!flip`sdate`sym`name`volume!flip tdate,:(`;`;0n) 
q)
/ and fill in the gaps 

q)fills s upsert delete rollover from r 
sdate     | sym name volume 
----------| --------------------- 
2010.01.01| VXZ4 someName4 400.4 
2010.01.02| VXZ4 someName4 400.4 
2010.01.03| VXZ4 someName4 400.4 
2010.01.04| VXG8 someName3 500.4 
2010.01.05| VXG8 someName3 600.6 
2010.01.06| VXG8 someName3 600.6 
2010.01.07| VXG8 someName3 600.6 
2010.01.08| VXG8 someName3 600.6 
2010.01.09| VXG8 someName3 600.6 
2010.01.10| VXG8 someName3 600.6 
2010.01.11| VXG8 someName3 600.6 
2010.01.12| VXG8 someName3 600.6 ..