Concatenating kdb Columns

Suppose in a query you need to concatenate two kdb columns into one; for example, to join date and time into one field - kdb has nifty features to do it easily.

  1. You can join(,) two or more column into one using '(each-both). The columns do not need to be of the same type. The type of the returned column is list.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    q)tab:([]firstname:`John`Jia`Jai`Jac;lastname:`James`Jain`Jadeja`Jones;age:1+4?30)
    q)tab
    firstname lastname age
    ----------------------
    John      James    19
    Jia       Jain     24
    Jai       Jadeja   25
    Jac       Jones    19
    q)
    q)select name:(firstname,'lastname) from tab
    name
    ----------
    John James
    Jia  Jain
    Jai  Jadeja
    Jac  Jones
    

  2. A mixed list is formed if the columns are of different type. If you cast the columns to string before each-both join, a concatenated string is formed. You can also insert a character using ,'.

    1
    2
    3
    4
    5
    6
    7
    q)select col:((string firstname),'(string lastname),'"/",'(string age)) from tab
    col
    --------------
    "JohnJames/19"
    "JiaJain/24"
    "JaiJadeja/25"
    "JacJones/19"
    

  3. If you need multi character delimiters, use sv. A nice advantage of this method is you don’t have to individually convert each column to string.

    1
    2
    3
    4
    5
    6
    7
    q)select col:({"--" sv x} each string (firstname,'lastname,'age)) from tab
    col
    -----------------
    "John--James--19"
    "Jia--Jain--24"
    "Jai--Jadeja--25"
    "Jac--Jones--19"
    

  4. To add a string as prefix and postfix to a column, you can use /:(each right) and \:(each left) respectively.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    q)select ("Dear ",/:(string firstname)) from tab
    firstname
    -----------
    "Dear John"
    "Dear Jia"
    "Dear Jai"
    "Dear Jac"
    q)select ((string age),\:" years") from tab
    x
    ----------
    "19 years"
    "24 years"
    "25 years"
    "19 years"
    q)select col:((string firstname),'(string lastname),'"/Age",/:(string age)) from tab
    col
    --------------
    "JohnJames/Age19"
    "JiaJain/Age24"
    "JaiJadeja/Age25"
    "JacJones/Age19"
    

  5. Lets conclude by creating a JavaScript style array by joining all three columns.

    1
    2
    3
    4
    5
    6
    7
    q)select ("['",/:(({"','" sv x} each string (firstname,'lastname,'age)),\:"']")) from tab
    x
    -----------------------
    "['John','James','19']"
    "['Jia','Jain','24']"
    "['Jai','Jadeja','25']"
    "['Jac','Jones','19']"
    

comments powered by Disqus