cancel
Showing results for 
Search instead for 
Did you mean: 

Add Linked Column to Parition

hoffmanroni
New Contributor III
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?
4 REPLIES 4

charlie
New Contributor II
New Contributor II
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}]

hoffmanroni
New Contributor III
Hi Charles,

I cant actually get this to work right.  When I modify add1col and call  q)addcol[`:/hdb;`trade;`link;{`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?

charlie
New Contributor II
New Contributor II
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:`a`b`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:`d`e`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 `a`b`c`d`e`f;id:`u`v`w`x`y`z)

/ 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[x],`link}`:db/2001.01.01/trade/.d
q){x set get[x],`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
--

hoffmanroni
New Contributor III
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.