CODEWITHSUNDEEP
databasekdb+
Akshay Damle
Akshay Damle

Reputation: 1250

KDB/Q: Cannot create a column storing lists in a KDB table

I am new to KDB and Q.

I read the following in Q For Mortals and deduced that compound columns are supported in KDB.

The case of a nested column in which all items are simple lists of the same type is handled specially in kdb+. We call this a compound column. There is no requirement that the simple lists all have the same length.

I know that one can create a new column in a table as follows: tablename: update newcol:value from tablename

When I set value as another column or some computation using other columns or a constant value, it works fine and a new column is created in the table with values set appropriately. However, when I try to set the value as a list, it does not work: tablename: update newcol: (1 2) from tablename gives a "length" error.

I went through the documentation in Q For Mortals but could not figure out how a compound column such as this is created. Please help me create a column which stores lists. Thanks!

Upvotes: 0

Views: 1526

Answers (3)

Jorge Sawyer
Jorge Sawyer

Reputation: 1341

Given some table

q)show tab:([]a:10?`AAA`BBB`CCC;b:10?10000);
a   b
--------
CCC 627
CCC 2685
BBB 1518
BBB 725
CCC 1369
CCC 663
BBB 9478
BBB 1588
AAA 8660
CCC 3780

If you want to add a constant compound column such as 1 2 you need to first extend this vector to the length of the table

q)count[tab]#enlist 1 2
1 2
1 2
1 2
1 2
1 2
1 2
1 2
1 2
1 2
1 2
q)update c:count[tab]#enlist 1 2 from tab
a   b    c
------------
CCC 627  1 2
CCC 2685 1 2
BBB 1518 1 2
BBB 725  1 2
CCC 1369 1 2
CCC 663  1 2
BBB 9478 1 2
BBB 1588 1 2
AAA 8660 1 2
CCC 3780 1 2

Other common ways for a compound column to be generated is when we update symbol fields to strings

q)update string a from tab
a     b
----------
"CCC" 627
"CCC" 2685
"BBB" 1518
"BBB" 725
"CCC" 1369
"CCC" 663
"BBB" 9478
"BBB" 1588
"AAA" 8660
"CCC" 3780

or when performing queries with a by clause but no aggregation

q)select b by a from tab
a  | b
---| ----------------------
AAA| ,8660
BBB| 1518 725 9478 1588
CCC| 627 2685 1369 663 3780

Upvotes: 2

David Edwards
David Edwards

Reputation: 96

Taking the example used in Q For Mortals:

q)tm:([] wk:2015.01.01 2015.01.08; rv:(38.92 67.34; 16.99 5.14 128.23 31.69))

We could add another new compound column to it as follows:

q)update newcol:(1 2;3 4 5) from tm
wk         rv                      newcol
-----------------------------------------
2015.01.01 38.92 67.34             1 2
2015.01.08 16.99 5.14 128.23 31.69 3 4 5

Note that we get a length error for example due to the mismatch if adding a different number of rows e.g. adding three rows (or lists) below

q)update newcol:(1 2;3 4 5;6 7 8) from tm
'length
  [0]  update newcol:(1 2;3 4 5;6 7 8) from tm

Upvotes: 2

cillianreilly
cillianreilly

Reputation: 2037

without seeing an example, this is likely because q is trying to use 1 2 as the values for newcol which doesn't have the same length as the table. To force the list itself as each entry in the table, you need to have an appropriate amount of values. You can achieve this using the virtual column i and enlist.

q)tab:([]time:5#.z.d;col1:1 2 3 4 5)
q)tab
time       col1
---------------
2020.07.17 1
2020.07.17 2
2020.07.17 3
2020.07.17 4
2020.07.17 5
q)update col2:1 2 from tab
'length
q)show tab:update col2:count[i]#enlist 1 2 from tab
time       col1 col2
--------------------
2020.07.17 1    1 2
2020.07.17 2    1 2
2020.07.17 3    1 2
2020.07.17 4    1 2
2020.07.17 5    1 2
q)meta tab
c   | t f a
----| -----
time| d
col1| j
col2| J

The meta of the table contains a capital J for col2, indicating that each entry is a list of longs.

Upvotes: 4

Related Questions