CODEWITHSUNDEEP
kdb+
duckman
duckman

Reputation: 747

KDB/Q: how to join and fill null with 0

I am joining 2 tables. How do I replace NULL with 0 a column from one of the table? My code to join

newTable: table1 lj xkey `date`sym xkey table2

I am aware that 0^ helps you to do this, but I dont know how to apply here

Upvotes: 0

Views: 982

Answers (2)

CWD
CWD

Reputation: 353

In future I recommend that you show examples of the 2 tables you have and the expected outcome you would like because it is slightly difficult to know but I think this might be what you want. First in your code you use xkey twice so it will throw an error. Change it to be:

newTable: table1 lj `date`sym xkey table2

Then for the updating of null values with a column from another tbl you could do:

q)tbl:([]date:.z.d;sym:10?`abc`xyz;data:10?8 2 0n)
q)tbl
date       sym data
-------------------
2020.12.10 xyz 8
2020.12.10 abc 8
2020.12.10 abc 8
2020.12.10 abc
2020.12.10 abc
2020.12.10 xyz 2
2020.12.10 abc 2
2020.12.10 xyz
2020.12.10 xyz
2020.12.10 abc 2
q)tbl2:([date:.z.d;sym:`abc`xyz];data2:2?100)
q)tbl2
date       sym| data2
--------------| -----
2020.12.10 abc| 23
2020.12.10 xyz| 46
q)select date,sym,data:data2^data from tbl lj `date`sym xkey tbl2 //Replace null values of data with data2.
date       sym data
-------------------
2020.12.10 xyz 8
2020.12.10 abc 8
2020.12.10 abc 8
2020.12.10 abc 23
2020.12.10 abc 23
2020.12.10 xyz 2
2020.12.10 abc 2
2020.12.10 xyz 46
2020.12.10 xyz 46
2020.12.10 abc 2

So, it's

Upvotes: 3

jomahony
jomahony

Reputation: 1692

Use within an an update statement, for example:

q)newTable:([]column1:(1;0Nj;2;0Nj))
q)update 0^column1 from newTable
column1
-------
1
0
2
0

Or functional form:

    q)newTable:([]column1:(1;0Nj;2;0Nj);column2:(1;2;3;0Nj))
    q)parse"update 0^column1 from newTable"
    !
    `newTable
    ()
    0b
    (,`column1)!,(^;0;`column1)
    q)![newTable;();0b;raze{enlist[x]!enlist(^;0;x)}each `column1`column2]
    column1 column2
    ---------------
    1       1
    0       2
    2       3
    0       0

Upvotes: 1

Related Questions