According to code.kx.com, fby is short for filter-by, and it is commonly used as the q equivalent to SQL’s HAVING clause (though, like where, fby is a q function, and its use is not limited to where clauses).

What fby does is aggregate values from one list based on groups defined in another, parallel, list. For example, suppose we have one list of cities and another list with a few temperature samples for each. We can use fby to calculate the minimum temperature sample for each city, and then replicate those values at each position for each corresponding city:

q)city:`NY`NY`LA`SF`LA`SF`NY
q)temp:32 31 75 69 70 68 12
q)(min;temp) fby city
12 12 70 68 70 68 12
q)

Thus, NY’s minimum temperature, 12, appears at every index in fby‘s output where `NY appears in city.

At the core of fby (like by) is group, which organizes the distinct values in a list into a dictionary mapping those values to their indices:

q)city:`NY`NY`LA`SF`LA`SF`NY
q)group city
NY| 0 1 6
LA| 2 4
SF| 3 5
q)

We can group the temperatures for each city together by indexing temp with the value of the grouped city dictionary:

q)grouped: value group city
q)temp[grouped]
32 31 12
75 70
69 68
q)

Note that the result of indexing temp with grouped is a nested list with the same shape as grouped. This is a general principle: the result of an indexing operation has the shape of the index.

Now we can apply an aggregation function to each of the temperature groups:

q)min each temp[grouped]
12 70 68
q)

We’re almost there. The real trick of fby is placing each aggregation result into a new list so that each element has the correct value per the grouping list. We can use @ (functional amend) to get the job done (see also the functional apply/amend faq):

q)@[temp; grouped; :; min each temp[grouped] ]
12 12 70 68 70 68 12
q)

The real fby is just slightly more complicated to ensure that the first argument to @ has the correct type.

Short answer:

A table is a reference to a (column) dictionary.


The internal representation of a table is nearly identical to that of a dictionary. We can use the flip command to create a table from a dictionary:

q)t: ([] a: 1 2 3; b: 4 5 6; c: 7 8 9)
q)d: `a`b`c ! (1 2 3; 4 5 6; 7 8 9)
q)t ~ flip d
1b
q)

In fact, when a dictionary is flip‘ed, the underlying core data structure remains untouched. The table itself is a simple, small object that refers to the original dictionary. Using .Q.w, we can measure how much more memory a table takes than the corresponding dictionary:

q).Q.w[]`used         // memory usage baseline
112992j
q)x:`a`b`c!3 3#til 9  // create a small dictionary
q).Q.w[]`used
113424j
q)x:flip x
q).Q.w[]`used         // memory usage delta is
113456j               // just 32 more bytes
q)

No matter how large the underlying dictionary is, creating a table is fast and still takes only 32 bytes:

q)x:`a`b`c!3 100000#til 10
q).Q.w[]`used
1686192j
q)x:flip x
q).Q.w[]`used
1686224j          // 32 = 1686224 - 1686192
q)

Now, Let’s examine how a keyed table is related to a dictionary. We start by creating a simple keyed table:

q)t: ([] a: 1 2 3; b: 4 5 6; c: 7 8 9)
q)keyedTable: `a`b xkey t
q)keyedTable
a b| c
---| -
1 4| 7
2 5| 8
3 6| 9
q)keys keyedTable
`a`b
q)

Since keyedTable is a table, one might expect it to have the same type as t but instead, q presents the following surprise:

q)type t
98h                  // type table as expected
q)type keyedTable
99h                  // *NOT* 98h
q)

Type 99h is the type number for dictionaries. If keyedTable really is a dictionary, we should be able to extract its key and value:

q)key keyedTable
a b
---
1 4
2 5
3 6
q)value keyedTable
c
-
7
8
9
q)

Indeed, keyedTable is a dictionary – one that holds unkeyed tables for both its key and its value:

q)type key keyedTable
98h
q)type value keyedTable
98h
q)

This suggests that we can create a keyed table by using the ! (dict) operator with two unkeyed tables:

q)(key keyedTable)!(value keyedTable)
a b| c
---| -
1 4| 7
2 5| 8
3 6| 9
q)

Lastly, joining the two flipped tables brings us back to the original dict.

q)(flip key keyedTable),(flip value keyedTable)
a| 1 2 3
b| 4 5 6
c| 7 8 9
q)

For more information, see Creating dictionaries and tables from C.