Note: You can now subscribe to our YouTube video channel for q/kdb+ tutorials.
Well, I will tell you what they are not – tables! If you take away one thing from this post, it’s that keyed tables are poorly named and are not tables. However, after reading this post, you should have a better understanding of why they are named keyed tables.
Reviewing tables and dictionaries
To really understand keyed tables, we first need to review dictionaries and tables. Recall that dictionaries are simply mappings between keys and values made up of lists.
Here is an example:
q)list_1:`price`size q)list_2:(100 200 300;10 20 30) q)dict:list_1!list_2 q)dict price| 100 200 300 size | 10 20 30 q)type dict 99h
If we take this dictionary and flip it, we will get a table.
q)flip dict price size ---------- 100 10 200 20 300 30 q)type flip dict 98h
Note how the type changed from 99h (dictionaries) to 98h (tables) when we flipped the dictionary.
A direct way to create tables is to use this popular syntax:
q)tab:([]price:100 200 300;size:10 20 30) q)tab price size ---------- 100 10 200 20 300 30 q)type tab 98h
What are keyed tables?
Keyed tables are dictionaries that, instead of mapping lists, map one table to another table. Its keys and values are both tables.
Let’s use some examples to illustrate what we just said. We will take our previous table, tab, and map it to a new table containing just one column using ! just as we would create a regular dictionary.
/ create a single column table q)single_col:([]sym:`AAPL`IBM`MSFT) q)single_col sym ---- AAPL IBM MSFT q)tab price size ---------- 100 10 200 20 300 30 / create a dictionary mapping two tables together q)key_tab:single_col!tab / output looks like a table q)key_tab sym | price size ----| ---------- AAPL| 100 10 IBM | 200 20 MSFT| 300 30 / but according to the type, it's a dictionary q)type key_tab 99h
Creating keyed tables
As we saw, we can take two tables and create a keyed table by mapping the two tables together. There is another way to create keyed tables and that’s by using the usual table syntax. You can simply put the keyed column within the brackets and rest of the syntax stays the same. Here is an example:
/ use table syntax but place keyed column within brackets q)key_tab_2:([sym:`AAPL`IBM`MSFT]price:100 200 300;size:10 20 30) q)key_tab_2 sym | price size ----| ---------- AAPL| 100 10 IBM | 200 20 MSFT| 300 30 q)type key_tab_2 99h
Converting a table to a keyed table
What if you already have a table and you want to convert it to a keyed table? q has a built-in keyword, xkey, to do that for you. Simply list the name of the columns you want keyed to the left and the name of the table to the right. Here is an example:
/ keying the sym column q)`sym xkey ([]sym:`AAPL`IBM`MSFT;price:100 200 300;size:10 20 30) sym | price size ----| ---------- AAPL| 100 10 IBM | 200 20 MSFT| 300 30 / you can also have multiple keys q)`sym`size xkey ([]sym:`AAPL`IBM`MSFT;price:100 200 300;size:10 20 30) sym size| price ---------| ----- AAPL 10 | 100 IBM 20 | 200 MSFT 30 | 300
Another way to convert a table to keyed table is by using ! operator. Instead of specifying the name of the columns you want to key, you specify their position. For example:
q)2!([]sym:`AAPL`IBM`MSFT;price:100 200 300;size:10 20 30) sym price| size ----------| ---- AAPL 100 | 10 IBM 200 | 20 MSFT 300 | 30
As you can see, this can lead to some unintended results so I recommend using xkey instead.
Converting a keyed table to a table
What if you wanted to go the other way and convert a keyed table to a regular table? You can still use xkey but this time you have to specify () as the left argument. That will remove any keys that your keyed table has which will convert it to a regular table. For example:
/ Here we first add keys and then remove them in one statement q)() xkey `sym`size xkey ([]sym:`AAPL`IBM`MSFT;price:100 200 300;size:10 20 30) sym size price --------------- AAPL 10 100 IBM 20 200 MSFT 30 300
We can do the same by using ‘!’ operator and specifying 0 as the left argument. For example:
q)0!`sym`size xkey ([]sym:`AAPL`IBM`MSFT;price:100 200 300;size:10 20 30) sym size price --------------- AAPL 10 100 IBM 20 200 MSFT 30 300
I hope this post helped clear any doubts you had about keyed tables. As mentioned in the beginning, always remember that keyed tables are not tables. They are dictionaries!