How to update kdb table transversely

Question

I have these two tables:

tab:([]col1:`abc`def`ghe`abc;val_00:`a`b`c`e;val_01:`d`e`f`t;val_02:`g`h`e`g;val_03:`r`t`y`o)
tab2:([]col1:`abc`abc`abc`abc`def`def`def`ghe`ghe`ghe;col2:0 1 2 3 4 5 6 7 8 9;col3:`Ashley`Peter`John`Molly`Apple`Orange`Banana`Robin`Tony`Bob)

and this is the result I am looking for:

tabResult:([]col1:`abc`def`ghe`abc;val_00:`Ashley`b`c`Ashley;val_01:`Peter`e`f`Peter;val_02:`John`h`e`John;val_03:`Molly`t`y`Molly)

    col1    val_00  val_01  val_02  val_03
    abc     Ashley  Peter   John    Molly
    def     b       e        h      t
    ghe     c       f        e      y
    abc     Ashley  Peter   John    Molly

I would like to update tab depending on tab2. If col1=`abc,col2=1 in tab2, I would like to update val_01 to `Peter in tab, and if col1 =`abc,col2=2 in tab2, I would like to update val_02 field with `John in tab etc.

This is what I have so far:

{![tab;enlist(=;`col1;enlist x);0b;(enlist y)!enlist z]} . (`abc;`val_01;)

The function above works if the field is numerical and I use a number as the last arg. However, I am not sure how to update symbols and how to generalise this function for all tables.

Answer 1

If I'm understanding your request correctly, you're trying to update a field that has a long type with values that are of symbol type. This is going to fail with a 'type error as column values are expected to be uniform in type. What you can alternatively do is create new columns for the symbol entries, and after that select the columns you want.

Is something like this what you had in mind? I've assumed that the column name is determined by its col2 value in tab. Also it looks like you have two val_01 columns in your tab input, I assumed one of these was supposed to be val_02.

q)(uj/){![tab;enlist(=;`col1;enlist x);0b;(enlist`$"val_0",string[y],"_sym")!enlist enlist z]}.'flip tab2`col1`col2`col3
col1 val_00 val_01 val_02 val_03 val_01_sym val_02_sym val_03_sym val_04_sym val_05_sym val_06_sym val_07_sym val_08_sym val_09_sym
-----------------------------------------------------------------------------------------------------------------------------------
abc  1      2      2      3      Peter
def  2      2      3      2
ghe  3      3      1      1
abc  1      2      2      3                 John
def  2      2      3      2
ghe  3      3      1      1
abc  1      2      2      3                            Molly
def  2      2      3      2
ghe  3      3      1      1
abc  1      2      2      3
def  2      2      3      2                                       Apple
ghe  3      3      1      1
abc  1      2      2      3
def  2      2      3      2                                                  Orange
ghe  3      3      1      1
abc  1      2      2      3
def  2      2      3      2                                                             Banana
ghe  3      3      1      1
abc  1      2      2      3
def  2      2      3      2
ghe  3      3      1      1                                                                        Robin
abc  1      2      2      3
def  2      2      3      2
ghe  3      3      1      1                                                                                   Tony
abc  1      2      2      3
def  2      2      3      2
ghe  3      3      1      1                                                                                              Bob

EDIT:

Based on your comments, I've amended my solution:

q)cols[tab]#{![x;enlist(=;`col1;enlist y`col1);0b;(enlist`$"val_0",string y`col2)!enlist enlist y`col3]}/[tab;tab2]
col1 val_00 val_01 val_02 val_03
--------------------------------
abc  Ashley Peter  John   Molly
def  b      e      h      t
ghe  c      f      e      y
abc  Ashley Peter  John   Molly