Complicated column names question

Community Support
1

Hello community!
I’m very new to KDB+.

I imported ASCII file to in-memory table. And now I’m trying to perform a select, but it does not work because column names contains spaces and colons.

select avg $"All: Net Profit" by $“Mon: distance_l” from smth

does not work either.

Please advice

2

Hi Paul and welcome to kdb+!

The issue is that standard select queries can’t deal with the column names having spaces. 

You could use a functional select but the easiest thing to do is rename the column names of the table using the keyword xcol.
Example usage can be seen on the Kx reference page: http://code.kx.com/q/ref/dictsandtables/#xcol

Thanks, 

Michael 

3

Hi Paul,

The easiest approach would probably be to rename the columns of your table to remove the problem characters. You can do this using the .Q.id function:
http://code.kx.com/wiki/DotQ/DotQDotid

Hope this helps,
Scott

4

Hi Paul

Have a look at the reference page for .Q.id.

This function purges bad characters from symbols and can be used in conjunction with your tables columns names:

q)smthAll: Net Profit Mon: distance_l-------------------------------1 a2 b3 cq)q).Q.id'[cols smth]xcol smthAllNetProfit Mondistance_l--------------------------1 a2 b3 c

Alternatively a custom function to rename the columns could be defined if you are not happy with the output above.

Please let us know if we can be any more help.

Regards,

Thomas Smyth

AquaQ Analytics

5

Hey Paul,

You can rename the columns as you are loading in the file like this:

tab:col1col2col3 xcol("III";enlist",")0:test.txt

You can then try your select statement with the renamed columns.

q)tab:col1col2col3 xcol ("III";enlist",")0:test.txt

q)tab

col1 col2 col3

1    2    3

2    3    4

5    6    7

q)select col1 by col2 from tab

col2| col1

----| ----

2   | 1

3   | 2

6   | 5

If you want to leave column names as is, things become more complicated and you 

will have to use a functional select, like so:

q)tab2:(“III”;enlist",")0:`test.txt

q)tab2

Col1 Col: 2 Col: 3

1    2      3

2    3      4

5    6      7

q)?[tab2;();0b;enlist[$“Col: 2”]!enlist `$“Col: 2”]

Col: 2

2

3

6

q)

Can find more info on them here

http://www.timestored.com/kdb-guides/functional-queries-dynamic-sql

Hope this helps,

Seán

6

Small correction, you can use .Q.id directly on the table:

q).Q.id smthAllNetProfit Mondistance_l--------------------------1 a2 b3 c

7

Thank you all, I haven’t expected so many replies!
I guess the applying .Q.id to the title only would be more effective if the table is big enough