According to, 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)temp:32 31 75 69 70 68 12
q)(min;temp) fby city
12 12 70 68 70 68 12

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)group city
NY| 0 1 6
LA| 2 4
SF| 3 5

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
32 31 12
75 70
69 68

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

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

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

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
q)x:`a`b`c!3 3#til 9  // create a small dictionary
q)x:flip x
q).Q.w[]`used         // memory usage delta is
113456j               // just 32 more bytes

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)x:flip x
1686224j          // 32 = 1686224 - 1686192

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
a b| c
---| -
1 4| 7
2 5| 8
3 6| 9
q)keys keyedTable

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

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

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

q)type key keyedTable
q)type value keyedTable

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

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

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

Typical q code operates on all of the elements of a container at once:

q)container: 1 2 3
q)100 * container
100 200 300

Sometimes we are interested in only a subset of elements from a container:

q)container: til 10
0 1 2 3 4 5 6 7 8 9
q)container[where 0 = container mod 2]
0 2 4 6 8

Sometimes, however, you need to update particular elements of a structure while leaving the remaining elements unchanged. That’s what functional apply and amend are for; they transform specific elements of a container without touching the others. The variations are distinguished by 3 choices:

1. @ or .

Which operator is used, @ or ., determines the interpretation of the indices used to select the elements to transform.

2. container or name

The first argument is either the value of a container or the name of a global variable referring to a container. In the former case, a new object is returned; in the latter, the global variable is modified and its name is returned.

3. monadic or dyadic function

If the transformation requires additional information beyond that contained in each element itself, that is accomplished by using a dyadic function and supplying the additional information in a fourth argument to the operator.

For a detailed discussion, please see the following faqs:

Functional @ (apply)

Functional . (apply)

Lastly, note that there is another pair of overloads for @ and . – each with three arguments – called protected execution, which are invoked when the first argument to @ or . is a function or projection; protected execution is discussed in another faq.