How to create table dynamically?

Community Support
1

Hi, Masters:
  I need to create tables based on current day’s stocks. For example, Nov 1, there’re 5 stocks(001~005), and the tables should be created as tbl001~tbl005; and Nov 3, there’re 10 stocks(001~010), the tables should be created as (tbl001~tbl010).

  So far, I can generate the tablename according to the stock name. But how to pass the tablename to KDB+ to create table?

Regards

Zheng

2

Hi,

Sounds like you want to name a table based on a symbol you have created. In order to do this you need to use ‘set’:

`tbl001 set (sym:())

Please let us know if this resolves your issue.

Regards,

Thomas Smyth

AquaQ Analytics

3

You could also use “set” along with an “each” to automatically split your table into separate tables, something like this (using a dummy trades table):<o:p></o:p>

<o:p> </o:p>

q)exec distinct sym from trades<o:p></o:p>

u#ORCLYHOOAAPLIBMNOKCSCOMSFTDELLGOOG<o:p></o:p>

q){sv[;x,trades] set select from trades where sym=x}each exec distinct sym from trades<o:p></o:p>

ORCL.tradesYHOO.tradesAAPL.tradesIBM.tradesNOK.tradesCSCO.tradesMSFT.tradesDELL.trades`GOOG.trades<o:p></o:p>

q)ORCL.trades<o:p></o:p>

time                          sym  src price size<o:p></o:p>

-------------------------------------------------<o:p></o:p>

2017.11.06D08:00:53.319000000 ORCL N   32.23 1099<o:p></o:p>

2017.11.06D08:02:31.085000000 ORCL O   32.18 514<o:p></o:p>

2017.11.06D08:04:51.572000000 ORCL O   32.23 25<o:p></o:p>

2017.11.06D08:06:07.993000000 ORCL L   32.21 965<o:p></o:p>

2017.11.06D08:19:32.241000000 ORCL N   32.22 5070<o:p></o:p>

2017.11.06D08:33:33.336000000 ORCL N   32.18 190<o:p></o:p>

..<o:p></o:p>

<o:p> </o:p>

Here sv is used to generate the table name in each case as a symbol, which is then used by set as a global variable name for the given table<o:p></o:p>

<o:p> </o:p>

Depending on your use case, it may make more sense to do this when loading data or receiving real time data – if this is the case, there are likely open source frameworks available that can ease this process.<o:p></o:p>

<o:p> </o:p>

Regards<o:p></o:p>

Jonathon<o:p></o:p>

<o:p> </o:p>

From: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] On Behalf Of thomas.smyth@aquaq.co.uk
Sent: 06 November 2017 09:23
To: Kdb+ Personal Developers <personal-kdbplus@googlegroups.com>
Subject: [personal kdb+] Re: How to create table dynamically?<o:p></o:p>

<o:p> </o:p>

Hi,<o:p></o:p>

<o:p> </o:p>

Sounds like you want to name a table based on a symbol you have created. In order to do this you need to use ‘set’:<o:p></o:p>

<o:p> </o:p>

`tbl001 set (sym:())<o:p></o:p>

<o:p> </o:p>

Please let us know if this resolves your issue.<o:p></o:p>

<o:p> </o:p>

Regards,<o:p></o:p>

Thomas Smyth<o:p></o:p>

AquaQ Analytics<o:p></o:p>

<o:p> </o:p>

On Monday, 6 November 2017 07:12:22 UTC, hzad…@gmail.com wrote:<o:p></o:p>

Hi, Masters:<o:p></o:p>

  I need to create tables based on current day’s stocks. For example, Nov 1, there’re 5 stocks(001~005), and the tables should be created as tbl001~tbl005; and Nov 3, there’re 10 stocks(001~010), the tables should be created as (tbl001~tbl010).<o:p></o:p>

  So far, I can generate the tablename according to the stock name. But how to pass the tablename to KDB+ to create table?<o:p></o:p>

<o:p> </o:p>

Regards<o:p></o:p>

Zheng<o:p></o:p>

–
Submitted via Google Groups

4

Alternatively, you could also create a single dictionary, whose keys are the stock symbols and values are the tables you want.

With this, you can easily address each table within the dictionary. (The global table names you wanted to create are just entries within the `. dictionary, anyway.)

5

Hi, Thomas:
  Thank you for your help!

  I tried the solution, it works at q platform. But while I create a function to do the job, it fails. the code of my function is:

  CreateTbl:{

    cntRICList: 5;

    indxRIC: 1;

    while[idxRIC < cntRICList;

        tblname: `$raze(“tbl”, string idxRIC);

        tblname set ([] RIC: symbol$(); Date: `date$());

        idxRIC+:1;

    }

  What’s wrong with my code of create table?

Thanks!

Zheng

? 2017?11?6??? UTC+8??5:23:28?thomas…@aquaq.co.uk???

Hi,

Sounds like you want to name a table based on a symbol you have created. In order to do this you need to use ‘set’:

`tbl001 set (sym:())

Please let us know if this resolves your issue.

Regards,

Thomas Smyth

AquaQ Analytics

6

Hi, Jonathon:
  Many thanks!

  I tried your solution. It works, great! This gives me another view to resolve problem.

  My understanding of this solution is: data stores in a table(maybe very large because of many realtime tick feed into it), and use the “sv” for each sym to create a sudo table for query data.

 

Thanks

Zheng

? 2017?11?6??? UTC+8??5:29:50?Jonathon McMurray???

You could also use “set” along with an “each” to automatically split your table into separate tables, something like this (using a dummy trades table):

 

q)exec distinct sym from trades

u#ORCLYHOOAAPLIBMNOK<wbr>CSCOMSFTDELLGOOG

q){sv[;x,trades] set select from trades where sym=x}each exec distinct sym from trades

ORCL.tradesYHOO.tradesAAPL.<wbr>tradesIBM.tradesNOK.tradesCSCO.tradesMSFT.tradesDELL.trades`GOOG.trades

q)ORCL.trades

time                          sym  src price size

-------------------------------------------------

2017.11.06D08:00:53.319000000 ORCL N   32.23 1099

2017.11.06D08:02:31.085000000 ORCL O   32.18 514

2017.11.06D08:04:51.572000000 ORCL O   32.23 25

2017.11.06D08:06:07.993000000 ORCL L   32.21 965

2017.11.06D08:19:32.241000000 ORCL N   32.22 5070

2017.11.06D08:33:33.336000000 ORCL N   32.18 190

..

 

Here sv is used to generate the table name in each case as a symbol, which is then used by set as a global variable name for the given table

 

Depending on your use case, it may make more sense to do this when loading data or receiving real time data – if this is the case, there are likely open source frameworks available that can ease this process.

 

Regards

Jonathon

 

From: personal…@googlegroups.com [mailto:personal…@googlegroups.com] On Behalf Of thomas…@aquaq.co.uk
Sent: 06 November 2017 09:23
To: Kdb+ Personal Developers <personal…@googlegroups.com>
Subject: [personal kdb+] Re: How to create table dynamically?

 

Hi,

 

Sounds like you want to name a table based on a symbol you have created. In order to do this you need to use ‘set’:

 

`tbl001 set (sym:())

 

Please let us know if this resolves your issue.

 

Regards,

Thomas Smyth

AquaQ Analytics

 

On Monday, 6 November 2017 07:12:22 UTC, hzad...@gmail.com wrote:

Hi, Masters:

  I need to create tables based on current day’s stocks. For example, Nov 1, there’re 5 stocks(001~005), and the tables should be created as tbl001~tbl005; and Nov 3, there’re 10 stocks(001~010), the tables should be created as (tbl001~tbl010).

  So far, I can generate the tablename according to the stock name. But how to pass the tablename to KDB+ to create table?

 

Regards

Zheng

–
Submitted via Google Groups

7

Hi, Flying:
  Thanks!

  Yes, it’s a good idea to create a mapping mechanism to identify the sym and it’s table.

Thanks

Zheng

? 2017?11?7??? UTC+8??10:21:49?Flying???

Alternatively, you could also create a single dictionary, whose keys are the stock symbols and values are the tables you want.

With this, you can easily address each table within the dictionary. (The global table names you wanted to create are just entries within the `. dictionary, anyway.)

8

Why do you prefer this method over a hard coded schema? At my company we have several thousand symbols across 6+ exchanges, and we use a different table for every type of market data (trade, quote ,etc). Enforcing a schema across exchanges and symbols is a good thing - No matter the exchange or product, every sym will have a “volume,” “price” ,“sym”, “utc_datetime” columns in the trades table.