Nested table in KDB

[Alex]

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?

[Alex]

I created the following:

q)Trade:([sym:()]data:([]datetime:();price:`float$();size:`int$()))
q)show meta Trade
c | t f a
----| -----
sym |
data|


When I tried to insert, I got the following error
q)`Trade insert (`IBM;(2003.03.23T09:10:35.000000;10f;400))
'length

[Charles Skelton]

you might find it useful to consider using a non-nested table, but with a `g# attribute on the sym column.

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To post to this group, send email to personal...@googlegroups.com.
To unsubscribe from this group, send email to personal-kdbpl...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/personal-kdbplus?hl=en.

[hlivni]

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)

[Alex]

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?

[hlivni]

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

[Alex]

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)]
'type
q)insert[`dict[`AAPL]] (2003.03.23T09:10:35.000000;10f;400)
'type
q)insert[dict[`AAPL];(2003.03.23T09:10:35.000000;10f;400)]
'type
q)insert[`dict[`AAPL]] (2003.03.23T09:10:35.000000;10f;400)
'type

[Alex]

Also,

I cannot use Select statement :
q)select from dict[`AAPL]

'type

On Jan 28, 3:34 am, hlivni <hagai.li...@gmail.com> wrote:

[hlivni]

the insert would not work, because it requires the target table to be
referenced (with the backtick, see https://code.kx.com/trac/wiki/QforMortals2/queries_q_sql#Insert
). you can use upsert, and assignment

dict[`AAPL]: upsert[dict[`AAPL];(2003.03.23T09:10:35.000000;10f;400)]

i'm not sure why select failed for you

q)select from dict[`AAPL]
datetime price size
----------------------------------
2003.03.23T09:10:35.000 10 400
2003.03.23T09:10:35.000 10 400
2003.03.23T09:10:35.000 10 400
2003.03.23T09:10:35.000 10 400

if you can't figure it out, post how you assigned the table to
dict[`AAPL]