How to do calculation between roles within groups in KDB+/Q elegantly

It is easy to do calculation between columns for KDB+ tables. Similar to SQL or like ‘mutate’ in the dplyr package of R, we can define the new columns easily. However, calculations between rows can be tough for me as a beginner. Below, I listed some of the methods I tried.

Loop over each group

If the table is not so large, we can loop over each group.

q) update NewColumn: func each OldColumn from Table
or
{[x] func} each TimeCommodityGroup

to do the calculation would be very slow and can be comlipated.

Adding new columns to faciliate calculation


Basics

  1. Start and quit environment
>q
KDB+ 4.0 2020.05.04 Copyright © 1993–2020 Kx Systems
q)\\

2. Assign variable

q)w:56                   / numerical variable
q)w
56
q)txt:"Hello World!" / string variable
q)txt
"Hello World!"
q)table1:([]x:1 2 3) / table variable
q)table1
x
-
1
2
3

Define tables

Create table with column names. An empty table without column type is defined with the following syntax:

q)table1:([]name:();gender:();age:();phone:())    /create table
q)table1
name gender age phone
---------------------

Use meta command to display information of the table.

c: column, t: type, f: foreign key, a: attribute

q)meta table1
c | t f a
------| -----
name |
gender|
age…

  1. 64-bit version on demand

Free, non-commercial, 64-bit kdb+ on demand in the following link

2. Download the latest version 4.0 for Windows

3. Unzip and put the license in the unzipped folder.

├── kc.lic
├── w64
│ └── q
└── q.k

4(a). Set environment variable QHOME and path

setx QHOME "C:\q"
setx PATH "%PATH%;C:\q\w64"
  • change “C:\q” to the path of your unzipped folder with kc.lic in it.
  • ‘license error: k4.lic’ when starting might be from failure to set up the environment variable correctly.

Or..

4(b). Go to control panel/ system/ Edit environment variables

  • Use “New…” button to add variable QHOME with…

PeterBanng

Cello dropout

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store