add column to table in kdb based of existing columns?

kdb+ append tables
kdb+ question mark
kdb+ attributes
kdb extract column from table
kdb rows to columns
kdb upsert
kdb copy table
kdb uj

I want to add a new column to a kdb table, it should add based of the existing column by populating with the non null value as below

q)t:([]a:`a`b`c`d`e`f`g`h;b:1 0n 3 4 0n 6 0n 8;c:0n 2 0n 0n 5 0n 7 0n)
q)t
a b c
-----
a 1  
b   2
c 3  
d 4  
e   5
f 6  
g   7
h 8  

I want to add a column d that would take the value from c or d that isn't null to produce a table like this

a b c d
-------
a 1   1
b   2 2
c 3   3
d 4   4
e   5 5
f 6   6
g   7 7
h 8   8

I tried concatenating but then it has the null in it:

q)update d:(b,'c)from t
a b c  d  
----------
a 1    1  
b   2    2
c 3    3  
d 4    4  
e   5    5
f 6    6  
g   7    7
h 8    8  

A vector conditional might be what you’re after, something like the below:

update d:?[null b;c;b] from t

You can read more about vector conditionals here. This expects a Boolean list as the first argument and returns values from a list in the second argument where True, or values from a list in the third argument where False. For example:

q)?[10101b;"abcde";"ABCDE"]
"aBcDe"

When used in conjunction with a select/update statement, columns of the table can be specified as the arguments to the vector conditional as these are simply lists.

As an aside, the null keyword returns a Boolean true where a value is null and is useful as part of your solution.

add a column to a table kdb, A column of new data by specifying the list of values 2. A new column that is calculated based on an existing column. ` q)table:([] a:1 2 3; b:”POI”) How can I add a column to a table in kdb? Is there an append column function? The easiest way to add a column is to use the update function. Below we show adding: 1. A column of new data by specifying the list of values 2. A new column that is calculated based on an existing column ` q)table:([] a:1 2 3; b:”POI”) q)table a b — 1 P 2 O 3 I. q)update newCol:8 9 10 from table a b newCol ———-1 P 8 2 O 9


You can use the ^(fill) operator.

t:([]a:`a`b`c`d`e`f`g`h;b:1 0n 3 4 0n 6 0n 8;c:0n 2 0n 0n 5 0n 7 0n)
q)update d:b^c from t
a b c d
-------
a 1   1
b   2 2
c 3   3
d 4   4
e   5 5
f 6   6
g   7 7
h 8   8

It is worth noting that if you had a row with non-null values for b and c then the query above would default to the value in c. If you would prefer the value in b to be default then switch the inputs:

q)t:([]a:`a`b`c`d`e`f`g`h;b:1 0n 3 4 0n 6 0n 8;c:0n 2 0n 0n 5 100 7 0n)
q)update d:b^c from t
a b c   d
-----------
a 1     1
b   2   2
c 3     3
d 4     4
e   5   5
f 6 100 100
g   7   7
h 8     8
q)update d:c^b from t
a b c   d
---------
a 1     1
b   2   2
c 3     3
d 4     4
e   5   5
f 6 100 6
g   7   7
h 8     8

Tables | Q For Mortals, The fact that q tables comprise ordered column lists makes kdb+ very efficient at For table columns (only), you can omit the leading semi-colon in the syntax. Now say we want to add a key column eid containing employee identifiers. The q interpreter can make certain optimizations based on the structure of the list  I have a query that creates a table splitting out sales data by department (tops, bottoms, jewelry, etc). I then set a date period and sum the total sales, items and costs for each department as individual columns. I'd now like to include a "Total" row to the table. I thought this could be handled by using insert:


You could use 'or(|)' operator.

  q)update d:b|c from t

Concat will give you a list with items from both 'b' and 'c' column. It will not remove null. 'or' will compare each pair of 'b' and 'c' and will return maximum value from that pair. As null is lesser than an integer, it will give you integer value either from 'b' or 'c' column.

9. Queries – q-sql - Q for Mortals - Q and kdb+, Syntax: tname insert records. Where. tname is a table name as a symbol atom; records is one or more records that match the table columns. inserts records into​  If you want to create a derived column from an existing column then you would also need to use the fncol function to apply a lambda to the column. For example to create a copy of a price column and then double all prices in the new column: copycol[`:hdb;`table;`price;`price_new]; fncol[`:hdb;`table;`price_new;{2*x}];


Can use fill here - https://code.kx.com/wiki/Reference/Caret

q)t:([]a:`a`b`c`d`e`f`g`h;b:1 0n 3 4 0n 6 0n 8;c:0n 2 0n 0n 5 0n 7 0n)
q)update d:c^b from t
a b c d
-------
a 1   1
b   2 2
c 3   3
...

insert – Reference – kdb+ and q documentation, A query on a table having a foreign key can access columns in the keyed table To add a new column of nulls to an existing splayed table, we first get the row Here we are decomposing based on the values in a symbol column, which we  8.1.4 Table Metadata. The column names of a table can be retrieved as a list of symbols with cols. q)cols t _ The function meta applied to a table retrieves its metadata. The result is a keyed table (see §8.4.1) with one record for each column in the original table. The key column c of the result contains the column names.


14. Introduction to Kdb+ - Q for Mortals, Can I give the column values when creating a table? An alternative to using multiple columns as a primary key is to add to the table a column of However, it is also possible for them to compute new columns or rename existing ones. First, you specify the name of the table, which you want to add the new column, after the ALTER TABLE clause. Second, you specify the column name, data type, and its constraint. Note that you cannot add a column that already exists in the table; trying to do so will cause an error.


Frequently-asked questions about kdb+ – Knowledge Base – kdb+ , update and Update are (respectively) a q keyword and a q operator that add rows or columns to a table. The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table. ALTER TABLE - ADD Column To add a column in a table, use the following syntax:


update, Update – Reference – kdb+ and q documentation, q)//Remove the existing foreign key from the trade table and add the to the single-column case is required to link tables together based on multiple columns. To add a new column to an existing table, you use the ALTER TABLE ADD COLUMN statement as follows: ALTER TABLE table_name ADD COLUMN new_column_name data_type; Let’s examine the statement in a greater detail. First, specify the table that you want to add a new column in the ALTER TABLE clause.