FAQ Database Discussion Community


(kdb+/q) Pivot table: date in rows, symbols in columns, last price as values

pivot-table,kdb,q-lang
General pivot function provided at http://code.kx.com/wiki/Pivot goes: piv:{[t;k;p;v;f;g] v:(),v; G:group flip k!(t:.Q.v t)k; F:group flip p!t p; count[k]!g[k;P;C]xcols 0!key[G]!flip(C:f[v]P:flip value flip key F)!raze {[i;j;k;x;y] a:count[x]#x 0N; a[y]:x y; b:count[x]#0b; b[y]:1b; c:a i; c[k]:first'[a[j]@'where'[b j]]; c}[I[;0];I J;J:where 1<>count'[I:value G]]/:\:[t v;value F]} given that f and g are defined as f:{[v;P]`$raze each...

Cannot allocate memory for a column of compound floats on a partitioned table

kdb,q-lang
I have a partitioned table in my hdb that includes a column containing large lists of floats (at most 400 floats per element). eg each element looks like (100.0 1.0 ...) When trying to select on this column from days where there are particularly high numbers of rows I get...

Use value to reference a variable in a function

kdb,q-lang
How can I get value to see a variable in a local scope? For example: a:2; func:{ a:1; value "a" } returns 2...

Select value from mixed-type row in KDB

kdb,q-lang
We defined our KDB table with the type C (i.e. Character array). The first value which we inserted had a type String. The second value had a type int (i.e. i). Now, when we try to query KDB with conditions where like="value" it doesn't work. Because we have mixed type...

How to convert character array into list in kdb

kdb,q-lang
We have KDB query which sources data from different columns and one of the columns has Character array type ('C'), i.e. it contains text. What we want is enlist it when we query for data like this select enlist column_with_character_arr from table i.e. to have ("value1") inside a list so...

Use a function in the groupby clause of a functional select

kdb,q-lang
Is it possible to include a function in the groupby section of functional select, similar to how the aggregations work? For example how would I turn select by `long$`timespan$04:00 xbar time from table into functional select?...

Delete/remove/drop variable in q

q-lang
I've created a variable in the : q)myvar: 1 How can I delete it? I've tried: q)delete myvar from `.z but the namespace `.z is clearly not the right one, as the variable still does exist: q)myvar 1 ...

Quicksort in Q/KDB+

kdb,q-lang
I found this quicksort implementation on a website: q:{$[2>distinct x;x;raze q each x where each not scan x < rand x]}; I don't understand this part: raze q each x where each not scan x < rand x Can someone explain it to me step by step?...

Updating point-in-time (bitemporal) table

kdb,q-lang
Let's have a table storing point-in-time (bitemporal) data: pit:([]dt:`date$();sym:`symbol$();val:`float$();stamp:`timestamp$()) Sample data might look like: `pit insert (2015.01.05 2015.01.06 2015.01.05;`IBM`IBM`MSFT;105.11 106.6 35.3; 2015.02.01D05:01:25.0 2015.02.01D05:01:25.0 2015.02.01D05:01:25.0) pit dt sym val stamp ---------------------------------------------------- 2015.01.05 IBM 105.11 2015.02.01D05:01:25.000000000 2015.01.06 IBM 106.6 2015.02.01D05:01:25.000000000 2015.01.05 MSFT 35.3 2015.02.01D05:01:25.000000000 For...

(kdb+/q) append to dictionary

kdb,q-lang
I am trying to programmatically construct arguments to functional select call having the form: ?[ `t; () ; groupBy; ()] The problematic part is groupBy, which should be a dictionary. Suppose the objective is to arrive at the parse tree in the form: parse "select by sym,month:`date$dt.month from t" I...

How to work with date and time in KDB

kdb,q-lang
I tried to work with dateDtimespan type by subtracting one dateDtimespan from another, but KDB (QPad) always shows 0 as a result, why? Also if I have, say, datetime 12.11.2014:22:33:00.000000000 in one column and only time 22:32:00.000000000 in another, how I may remove date part from the first column to...