hi,I would like to create a nested table structure in KDB…e.g. something like:Trade:(sym:())each Trade[`sym] will have the schema datetime:();price:`float$();size:`int$()Is it possible?
I created the following:q)Trade:([sym:()]data:(datetime:();price:float$();size:int$()))q)show meta Tradec | t f a----| -----sym |data|When I tried to insert, I got the following errorq)Trade insert (IBM;(2003.03.23T09:10:35.000000;10f;400))'lengthOn Jan 27, 11:49?pm, Alex <alexc…> wrote:> hi,>> I would like to create a nested table structure in KDB…>> e.g. something like:>> Trade:(sym:())>> each Trade[`sym] will have the schema ?datetime:();price:`float$> ();size:`int$()>> Is it possible?</alexc…>
you might find it useful to consider using a non-nested table, but with a `g# attribute on the sym column.
I feel like there has to be a more q’y way of achieving what youneed. If you want to explain it more, maybe we could help.about your attempt:Trade:([sym:()]data:(datetime:();price:float$();size:int$()))what you do here is define an empty table of two undefined columns.the schema that you define for the data column will not be consideredwhen you try to insert into TradeEvery time you want to insert, you’ll need to create a tableTrade insert (IBM;(datetime:enlist2003.03.23T09:10:35.000000;price:enlist 10f;size:enlist 400))Since your initial schema leaves ‘data’ undefined, you’re free toinsert anything to it:Trade insert (AAPL;0)On Jan 27, 9:49?am, Alex <alexc…> wrote:> hi,>> I would like to create a nested table structure in KDB…>> e.g. something like:>> Trade:(sym:())>> each Trade[`sym] will have the schema ?datetime:();price:`float$> ();size:`int$()>> Is it possible?</alexc…>
Thanks for the help!I would like to explain what I need.I have an existing platform that will read the stock data fromindividual files and the data access layer was design so that theywill treat each stock symbol as individual table.The existing API allow the access / modify the data in the file by theindex.e.g. IBM[0] will return the first OHLC entry of IBM.I would like to migrate the stock data from file to KDB. However, Iwould like to use 1 big table to store Trade data for all stocksinstead of having individual files for each stock.I also want to reuse the existing API so that the data of individualstock can be access by row index.I can create a VIEW on KDB table but the VIEW is not modifiable.Thus, I am thinking to have a nested table so that I can look up theindividual table via the stock code in KDB e.g:q) Trade[IBM]will return:q) 2003.03.23T09:10:35.000000;10.0;100 2003.03.23T09:10:36.000000;10.2;400 2003.03.23T09:10:37.000000;9.9;100 2003.03.23T09:10:38.000000;10.1;200 2003.03.23T09:10:39.000000;10.2;500And Trade[IBM][0]will return 2003.03.23T09:10:35.000000;10.0;100Is it possible to do so?On Jan 28, 2:51?am, hlivni <hagai.li…> wrote:> I feel like there has to be a more q’y way of achieving what you> need. ?If you want to explain it more, maybe we could help.>> about your attempt:>> Trade:([sym:()]data:(datetime:();price:float$();size:int$()))>> what you do here is define an empty table of two undefined columns.> the schema that you define for the data column will not be considered> when you try to insert into Trade>> Every time you want to insert, you’ll need to create a table>> Trade insert (IBM;(datetime:enlist> 2003.03.23T09:10:35.000000;price:enlist 10f;size:enlist 400))>> Since your initial schema leaves ‘data’ undefined, you’re free to> insert anything to it:> Trade insert (AAPL;0)>> On Jan 27, 9:49?am, Alex <alexc…> wrote:>> > hi,>> > I would like to create a nested table structure in KDB…>> > e.g. something like:>> > Trade:(sym:())>> > each Trade[`sym] will have the schema ?datetime:();price:`float$> > ();size:`int$()>> > Is it possible?</alexc…></hagai.li…>
See if the following might work for you:dict: () ! () / dictionary for symbols -> tablesdict[AAPL]:([]datetime:3# 2003.03.23T09:10:35.000000;price:3#10f;size:3#400)dict[AAPL] / shows entire tabledict[AAPL][0] / shows first record of tabledict[AAPL]:update price:4.2 from dict[AAPL] where i=0 / update atable based on index number, and persist changedict[AAPL] / shows entire tableOn Jan 27, 1:16?pm, Alex <alexc…> wrote:> Thanks for the help!>> I would like to explain what I need.>> I have an existing platform that will read the stock data from> individual files and the data access layer was design so that they> will treat each stock symbol as individual table.>> The existing API allow the access / modify the data in the file by the> index.>> e.g. IBM[0] ?will return the first OHLC entry of IBM.>> I would like to migrate the stock data from file to KDB. However, I> would like to use 1 big table to store Trade data for all stocks> instead of having individual files for each stock.>> I also want to reuse the existing API so that the data of individual> stock can be access by row index.>> I can create a VIEW on KDB table but the VIEW is not modifiable.>> Thus, I am thinking to have a nested table so that I can look up the> individual table via the stock code in KDB e.g:>> q) ?Trade[IBM]>> will return:>> q) 2003.03.23T09:10:35.000000;10.0;100> ? ? 2003.03.23T09:10:36.000000;10.2;400> ? ? 2003.03.23T09:10:37.000000;9.9;100> ? ? 2003.03.23T09:10:38.000000;10.1;200> ? ? 2003.03.23T09:10:39.000000;10.2;500>> And Trade[IBM][0]>> will return 2003.03.23T09:10:35.000000;10.0;100>> Is it possible to do so?>> On Jan 28, 2:51?am, hlivni <hagai.li…> wrote:>> > I feel like there has to be a more q’y way of achieving what you> > need. ?If you want to explain it more, maybe we could help.>> > about your attempt:>> > Trade:([sym:()]data:(datetime:();price:float$();size:int$()))>> > what you do here is define an empty table of two undefined columns.> > the schema that you define for the data column will not be considered> > when you try to insert into Trade>> > Every time you want to insert, you’ll need to create a table>> > Trade insert (IBM;(datetime:enlist> > 2003.03.23T09:10:35.000000;price:enlist 10f;size:enlist 400))>> > Since your initial schema leaves ‘data’ undefined, you’re free to> > insert anything to it:> > Trade insert (AAPL;0)>> > On Jan 27, 9:49?am, Alex <alexc…> wrote:>> > > hi,>> > > I would like to create a nested table structure in KDB…>> > > e.g. something like:>> > > Trade:(sym:())>> > > each Trade[`sym] will have the schema ?datetime:();price:`float$> > > ();size:`int$()>> > > Is it possible?</alexc…></hagai.li…></alexc…>
Thanks!Do all features supported by table can be used in dict[AAPL] ?I cannot insert new record to dict[AAPL]q)insert[dict[AAPL];(2003.03.23T09:10:35.000000;10f;400)]'typeq)insert[dict[AAPL]] (2003.03.23T09:10:35.000000;10f;400)'typeq)insert[dict[AAPL];(2003.03.23T09:10:35.000000;10f;400)]'typeq)insert[dict[AAPL]] (2003.03.23T09:10:35.000000;10f;400)'typeOn Jan 28, 3:34?am, hlivni <hagai.li…> wrote:> See if the following might work for you:>> dict: () ! () ? ?/ ?dictionary for symbols -> tables> dict[AAPL]:([]datetime:3# 2003.03.23T09:10:35.000000;price:3#> 10f;size:3#400)> dict[AAPL] ? ? / shows entire table> dict[AAPL][0] ?/ shows first record of table> dict[AAPL]:update price:4.2 from dict[AAPL] where i=0 ? / update a> table based on index number, and persist change> dict[AAPL] ? ? / shows entire table>> On Jan 27, 1:16?pm, Alex <alexc…> wrote:>> > Thanks for the help!>> > I would like to explain what I need.>> > I have an existing platform that will read the stock data from> > individual files and the data access layer was design so that they> > will treat each stock symbol as individual table.>> > The existing API allow the access / modify the data in the file by the> > index.>> > e.g. IBM[0] ?will return the first OHLC entry of IBM.>> > I would like to migrate the stock data from file to KDB. However, I> > would like to use 1 big table to store Trade data for all stocks> > instead of having individual files for each stock.>> > I also want to reuse the existing API so that the data of individual> > stock can be access by row index.>> > I can create a VIEW on KDB table but the VIEW is not modifiable.>> > Thus, I am thinking to have a nested table so that I can look up the> > individual table via the stock code in KDB e.g:>> > q) ?Trade[IBM]>> > will return:>> > q) 2003.03.23T09:10:35.000000;10.0;100> > ? ? 2003.03.23T09:10:36.000000;10.2;400> > ? ? 2003.03.23T09:10:37.000000;9.9;100> > ? ? 2003.03.23T09:10:38.000000;10.1;200> > ? ? 2003.03.23T09:10:39.000000;10.2;500>> > And Trade[IBM][0]>> > will return 2003.03.23T09:10:35.000000;10.0;100>> > Is it possible to do so?>> > On Jan 28, 2:51?am, hlivni <hagai.li…> wrote:>> > > I feel like there has to be a more q’y way of achieving what you> > > need. ?If you want to explain it more, maybe we could help.>> > > about your attempt:>> > > Trade:([sym:()]data:(datetime:();price:float$();size:int$()))>> > > what you do here is define an empty table of two undefined columns.> > > the schema that you define for the data column will not be considered> > > when you try to insert into Trade>> > > Every time you want to insert, you’ll need to create a table>> > > Trade insert (IBM;(datetime:enlist> > > 2003.03.23T09:10:35.000000;price:enlist 10f;size:enlist 400))>> > > Since your initial schema leaves ‘data’ undefined, you’re free to> > > insert anything to it:> > > Trade insert (AAPL;0)>> > > On Jan 27, 9:49?am, Alex <alexc…> wrote:>> > > > hi,>> > > > I would like to create a nested table structure in KDB…>> > > > e.g. something like:>> > > > Trade:(sym:())>> > > > each Trade[`sym] will have the schema ?datetime:();price:`float$> > > > ();size:`int$()>> > > > Is it possible?</alexc…></hagai.li…></alexc…></hagai.li…>
Also,I cannot use Select statement :q)select from dict[AAPL]'typeOn Jan 28, 3:34?am, hlivni <hagai.li...> wrote:> See if the following might work for you:>> dict: () ! () ? ?/ ?dictionary for symbols -> tables> dict[AAPL]:(datetime:3# 2003.03.23T09:10:35.000000;price:3#> 10f;size:3#400)> dict[AAPL] ? ? / shows entire table> dict[AAPL][0] ?/ shows first record of table> dict[AAPL]:update price:4.2 from dict[AAPL] where i=0 ? / update a> table based on index number, and persist change> dict[AAPL] ? ? / shows entire table>> On Jan 27, 1:16?pm, Alex <alexc...> wrote:>> > Thanks for the help!>> > I would like to explain what I need.>> > I have an existing platform that will read the stock data from> > individual files and the data access layer was design so that they> > will treat each stock symbol as individual table.>> > The existing API allow the access / modify the data in the file by the> > index.>> > e.g. IBM[0] ?will return the first OHLC entry of IBM.>> > I would like to migrate the stock data from file to KDB. However, I> > would like to use 1 big table to store Trade data for all stocks> > instead of having individual files for each stock.>> > I also want to reuse the existing API so that the data of individual> > stock can be access by row index.>> > I can create a VIEW on KDB table but the VIEW is not modifiable.>> > Thus, I am thinking to have a nested table so that I can look up the> > individual table via the stock code in KDB e.g:>> > q) ?Trade[IBM]>> > will return:>> > q) 2003.03.23T09:10:35.000000;10.0;100> > ? ? 2003.03.23T09:10:36.000000;10.2;400> > ? ? 2003.03.23T09:10:37.000000;9.9;100> > ? ? 2003.03.23T09:10:38.000000;10.1;200> > ? ? 2003.03.23T09:10:39.000000;10.2;500>> > And Trade[IBM][0]>> > will return 2003.03.23T09:10:35.000000;10.0;100>> > Is it possible to do so?>> > On Jan 28, 2:51?am, hlivni <hagai.li...> wrote:>> > > I feel like there has to be a more q'y way of achieving what you> > > need. ?If you want to explain it more, maybe we could help.>> > > about your attempt:>> > > Trade:([sym:()]data:([]datetime:();price:float$();size:int$()))>> > > what you do here is define an empty table of two undefined columns.> > > the schema that you define for the data column will not be considered> > > when you try to insert into Trade>> > > Every time you want to insert, you'll need to create a table>> > > Trade insert (IBM;([]datetime:enlist> > > 2003.03.23T09:10:35.000000;price:enlist 10f;size:enlist 400))>> > > Since your initial schema leaves 'data' undefined, you're free to> > > insert anything to it:> > > Trade insert (AAPL;0)>> > > On Jan 27, 9:49?am, Alex <alexc...> wrote:>> > > > hi,>> > > > I would like to create a nested table structure in KDB...>> > > > e.g. something like:>> > > > Trade:([sym:()]([]datetime:();price:float$();size:int$()))>> > > > each Trade[sym] will have the schema ?datetime:();price:float$> > > > ();size:int$()>> > > > Is it possible?</alexc…></hagai.li…></alexc…></hagai.li…>
the insert would not work, because it requires the target table to bereferenced (with the backtick, see https://code.kx.com/trac/wiki/QforMortals2/queries\_q\_sql#Insert). you can use upsert, and assignmentdict[AAPL]: upsert[dict[AAPL];(2003.03.23T09:10:35.000000;10f;400)]i’m not sure why select failed for youq)select from dict[AAPL]datetime price size----------------------------------2003.03.23T09:10:35.000 10 4002003.03.23T09:10:35.000 10 4002003.03.23T09:10:35.000 10 4002003.03.23T09:10:35.000 10 400if you can't figure it out, post how you assigned the table todict[AAPL]On Jan 27, 1:50?pm, Alex <alexc…> wrote:> Also,>> I cannot use Select statement :> q)select from dict[AAPL]> 'type>> On Jan 28, 3:34?am, hlivni <hagai.li...> wrote:>> > See if the following might work for you:>> > dict: () ! () ? ?/ ?dictionary for symbols -> tables> > dict[AAPL]:(datetime:3# 2003.03.23T09:10:35.000000;price:3#> > 10f;size:3#400)> > dict[AAPL] ? ? / shows entire table> > dict[AAPL][0] ?/ shows first record of table> > dict[AAPL]:update price:4.2 from dict[AAPL] where i=0 ? / update a> > table based on index number, and persist change> > dict[AAPL] ? ? / shows entire table>> > On Jan 27, 1:16?pm, Alex <alexc...> wrote:>> > > Thanks for the help!>> > > I would like to explain what I need.>> > > I have an existing platform that will read the stock data from> > > individual files and the data access layer was design so that they> > > will treat each stock symbol as individual table.>> > > The existing API allow the access / modify the data in the file by the> > > index.>> > > e.g. IBM[0] ?will return the first OHLC entry of IBM.>> > > I would like to migrate the stock data from file to KDB. However, I> > > would like to use 1 big table to store Trade data for all stocks> > > instead of having individual files for each stock.>> > > I also want to reuse the existing API so that the data of individual> > > stock can be access by row index.>> > > I can create a VIEW on KDB table but the VIEW is not modifiable.>> > > Thus, I am thinking to have a nested table so that I can look up the> > > individual table via the stock code in KDB e.g:>> > > q) ?Trade[IBM]>> > > will return:>> > > q) 2003.03.23T09:10:35.000000;10.0;100> > > ? ? 2003.03.23T09:10:36.000000;10.2;400> > > ? ? 2003.03.23T09:10:37.000000;9.9;100> > > ? ? 2003.03.23T09:10:38.000000;10.1;200> > > ? ? 2003.03.23T09:10:39.000000;10.2;500>> > > And Trade[IBM][0]>> > > will return 2003.03.23T09:10:35.000000;10.0;100>> > > Is it possible to do so?>> > > On Jan 28, 2:51?am, hlivni <hagai.li...> wrote:>> > > > I feel like there has to be a more q'y way of achieving what you> > > > need. ?If you want to explain it more, maybe we could help.>> > > > about your attempt:>> > > > Trade:([sym:()]data:([]datetime:();price:float$();size:int$()))>> > > > what you do here is define an empty table of two undefined columns.> > > > the schema that you define for the data column will not be considered> > > > when you try to insert into Trade>> > > > Every time you want to insert, you'll need to create a table>> > > > Trade insert (IBM;([]datetime:enlist> > > > 2003.03.23T09:10:35.000000;price:enlist 10f;size:enlist 400))>> > > > Since your initial schema leaves 'data' undefined, you're free to> > > > insert anything to it:> > > > Trade insert (AAPL;0)>> > > > On Jan 27, 9:49?am, Alex <alexc...> wrote:>> > > > > hi,>> > > > > I would like to create a nested table structure in KDB...>> > > > > e.g. something like:>> > > > > Trade:([sym:()]([]datetime:();price:float$();size:int$()))>> > > > > each Trade[sym] will have the schema ?datetime:();price:float$> > > > > ();size:int$()>> > > > > Is it possible?</alexc…></hagai.li…></alexc…></hagai.li…></alexc…>