Data.table join with roll = "nearest" returns a "search value" instead of the original value

Question

I am having a problem with the binary search function J() and roll = "nearest" .

Say I got this example data.table "dt"

 Key Value1 Value2 20 4 5 12 2 1 55 10 7 

I search with roll = "nearest" :

 dt[J(15), roll = "nearest"] 

... which returns:

 Key Value1 Value2 15 2 1 

Thus, the correct string is returned. However, the original value of "Key" (12) is replaced by the value used in the search (15).

My question is what is normal behavior and is it possible to change this automatic override?

EDIT:

Playable example (note I'm using version 1.9.7):

 library("data.table") dt <- data.table(c(20,12,55), c(4,2,10), c(5,1,7)) dt # V1 V2 V3 #1: 20 4 5 #2: 12 2 1 #3: 55 10 7 setkey(dt, V1) dt[J(15), roll = "nearest"] # V1 V2 V3 #1: 15 2 1 

Answer 1

You will probably need data.table in 1.9.7 to make x.V1 work. You can then explicitly reference a column from x of the dataset. This is necessary because the columns used in the connection are taken from the second data set i , since it is in the database R.

 library("data.table") dt <- data.table(c(20,12,55), c(4,2,10), c(5,1,7)) setkey(dt, V1) dt[J(15), .(V1=x.V1, V2, V3), roll = "nearest"] # V1 V2 V3 #1: 12 2 1 

As you mentioned, you already have 1.9.7, for others that don't have a wiki installation .