Skip to content

^ Coalesce

Merge keyed tables ignoring nulls

Syntax: x^y, ^[x;y]

Where x and y are keyed tables, returns them merged. With no nulls in y, the result is the same as for Join.

q)kt1:([k:1 2 3] c1:10 20 30;c2:`a`b`c)
q)kt2:([k:3 4 5] c1:300 400 500;c2:`cc`dd`ee)

q)kt1^kt2
k| c1  c2
-| ------
1| 10  a
2| 20  b
3| 300 cc
4| 400 dd
5| 500 ee

q)(kt1^kt2) ~ kt1,kt2
1b

^ Fill where x and y are lists or dictionaries

When y has null column values, the column values of x are updated only with non-null values of y.

q)kt3:([k:2 3] c1:0N 3000;c2:`bbb`)
q)kt3
k| c1   c2
-| --------
2|      bbb
3| 3000

q)kt1,kt3
k| c1   c2
-| --------
1| 10   a
2|      bbb
3| 3000

q)kt1^kt3
k| c1   c2
-| --------
1| 10   a
2| 20   bbb
3| 3000 c

The performance of Coalesce is slower than that of Join since each column value of y must be checked for null.


Joins