CODEWITHSUNDEEP
kdb+
mmv456
mmv456

Reputation: 23

How can I add a new column to the middle of a kdb table?

I have a table in kdb/Q that looks like this:

table_a:([] Symbol:`X`Y`Z; StartDate:2023.09.01 2023.09.02 2023.09.03;Name:`Maria`Sam`Sydney);

It gives me a table that looks like this:

Symbol StartDate Name
X 2023.09.01 Maria
Y 2023.09.02 Sam
Z 2023.09.03 Sydney

What I would like is to create a new column in between the StartDate and Name columns, so somethig that looks like this:

Symbol StartDate EndDate Name
X 2023.09.01 2023.09.01 Maria
Y 2023.09.02 2023.09.08 Sam
Z 2023.09.03 2023.09.14 Sydney

I looked up documentation, but it looks like there's only options to append rows to the ends of tables. Is there a way for me to add a new column in between using Q, or do I have to reconstruct the current table?

Upvotes: 0

Views: 417

Answers (2)

terrylynch
terrylynch

Reputation: 13612

If you have a small number of columns (that can reasonably be typed out) then you can always re-arrange columns - and create new ones - using a select statement rather than an update

q)select Symbol,StartDate,EndDate:StartDate+1,Name from table_a
Symbol StartDate  EndDate    Name
-----------------------------------
X      2023.09.01 2023.09.02 Maria
Y      2023.09.02 2023.09.03 Sam
Z      2023.09.03 2023.09.04 Sydney

(this assumes you're doing an unfiltered update, e.g. no where clause)

If you need it more generalised you would use an xcols-based solution as rianoc showed

Upvotes: 4

rianoc
rianoc

Reputation: 3746

Updates will put new columns on the far right.

You can use xcols to move columns to the left.

q)table_a:update EndDate:StartDate+1 from table_a
q)`Symbol`StartDate`EndDate xcols table_a
Symbol StartDate  EndDate    Name
-----------------------------------
X      2023.09.01 2023.09.02 Maria
Y      2023.09.02 2023.09.03 Sam
Z      2023.09.03 2023.09.04 Sydney

A quick function could be written:

q)moveToRightOf:{c:`,cols[x] except z;((((1+c?y)#c),z) except `) xcols x}

//Move EnDate to right of StartDate
q)moveToRightOf[table_a;`StartDate;`EndDate]
Symbol StartDate  EndDate    Name
-----------------------------------
X      2023.09.01 2023.09.02 Maria
Y      2023.09.02 2023.09.03 Sam
Z      2023.09.03 2023.09.04 Sydney

//Use ` to move to beginning
q)moveToRightOf[table_a;`;`EndDate]
EndDate    Symbol StartDate  Name
-----------------------------------
2023.09.02 X      2023.09.01 Maria
2023.09.03 Y      2023.09.02 Sam
2023.09.04 Z      2023.09.03 Sydney

moveToLeftOf is very similar but uses ` to move to the end

q)moveToLeftOf:{c:cols[x] except z;(((c?y)#c),z) xcols x}

q)moveToLeftOf[table_a;`Name;`EndDate]
Symbol StartDate  EndDate    Name
-----------------------------------
X      2023.09.01 2023.09.02 Maria
Y      2023.09.02 2023.09.03 Sam
Z      2023.09.03 2023.09.04 Sydney

q)moveToLeftOf[table_a;`Symbol;`EndDate]
EndDate    Symbol StartDate  Name
-----------------------------------
2023.09.02 X      2023.09.01 Maria
2023.09.03 Y      2023.09.02 Sam
2023.09.04 Z      2023.09.03 Sydney

q)moveToLeftOf[table_a;`;`EndDate]
Symbol StartDate  Name   EndDate
-----------------------------------
X      2023.09.01 Maria  2023.09.02
Y      2023.09.02 Sam    2023.09.03
Z      2023.09.03 Sydney 2023.09.04

Upvotes: 5

Related Questions