Add Linked Column to Parition

Community Support
1

Hello,

I am trying to add a linked column to an already existing partitioned db.  I am trying to use addcol from dbmaint.q to add the column to all the tables and then add the link to a master sym table.

I have tried     

addcol[:/hdb;trade;link;mas!(mas.sym)?sym]

this adds the link but does not give me the correct value in the row.  I think what i need to do is get the index of the sym in `trade set that as the default value but I’m unsure how.  Any ideas?

2

Hi Roni,

you have to apply that

`mas!(mas.sym)?sym

to each partition. You could modify add1col to take a func, something like

add1col:{[tabledir;colname;defaultvalue]

 if[not colname in ac:allcols tabledir;

  stdout"adding column “,(string colname),” (type “,(string type defaultvalue),”) to `",string tabledir;

  num:count get(`)sv tabledir,first ac;

  .[(`)sv tabledir,colname;();:;$[100h=type defaultvalue;defaultvalue tabledir;num#defaultvalue]];

  @[tabledir;`.d;,;colname]]}

and then call as

q)addcol[:/hdb;trade;link;{mas!mas.sym?get()sv x,sym}]

3

Hi Charles,

I cant actually get this to work right.  When I modify add1col and call  q)addcol[:/hdb;trade;link;{<wbr>mas!mas.sym?get()sv x,sym}] my link column gets added but its size is very large especially compared to the rest of my columns. When I try querying trade table I get wsfull (32b).

I think what may be happening is I am putting the sym enumeration in each row, but I cant be sure because I cant check without getting wsfull.  Because I am setting the default value to (get()sv :/hdb,`sym).

What I want is to link the column but inside the link column set the default value to the index of sym.

Does that make sense?

4

I think you are aiming for the following? These are the manual steps (which can be easily automated). If not the same, can you give a script to create a sample db which matches yours?

/ create example data

q):db/2001.01.01/trade/ set .Q.en[:db;](sym:ab`c;time:10:00:00.000+0 1 2;price:100+0 1 2;size:1000+0 1 2)

q):db/2001.01.02/trade/ set .Q.en[:db;](sym:de`f;time:10:00:00.000+0 1 2;price:100+0 1 2;size:1000+0 1 2)

q):db/mas/ set .Q.en[:db;](sym:reverse abcdef;id:uvwxyz)

/ now create the links

q):db/2001.01.01/trade/link set mas!get[:db/mas/sym]?get:db/2001.01.01/trade/sym

q):db/2001.01.02/trade/link set mas!get[:db/mas/sym]?get:db/2001.01.02/trade/sym

/ add link file to column list

q){x set get,link}:db/2001.01.01/trade/.d

q){x set get,link}:db/2001.01.02/trade/.d

q)\l db

q)select from trade

date       sym time         price size link

2001.01.01 a   10:00:00.000 100   1000 5   

2001.01.01 b   10:00:00.001 101   1001 4   

2001.01.01 c   10:00:00.002 102   1002 3   

2001.01.02 d   10:00:00.000 100   1000 2   

2001.01.02 e   10:00:00.001 101   1001 1   

2001.01.02 f   10:00:00.002 102   1002 0   

q)mas

sym id

f   u 

e   v 

d   w 

c   x 

b   y 

a   z 

q)select link.id from trade

id

–

z 

y 

x 

w 

v 

u 

5

Oh thanks Charles that helped.  I got it now.  Was working with a test db and I think I had something in there messing it up.