r - Understanding non-equi joins in data.table -

i’m attempting break down how non-equi joins work within data.table package. have following data:

library(dplyr) library(data.table)  sales <-  data.frame(salesx = c(3000, 2250,850,1800,1700,560,58,200,965,1525)                      ,week = seq(from = 1, = 10, = 1)                      ,uplift = c(0.04)                      ,slope = c(100)                      ,carryover = c(.35)) spend <- data.frame(spend = seq(from = 1, = 50000, = 1)) tempdata <- merge(spend,sales,all=true) 

the following solution output i’m looking to. have kept non-equi join variables separate now:

breakdown <- setdt(tempdata)[tempdata, .(spend, uplift, slope,carryover,salesx, week, i.week,x.week, i.salesx,x.salesx, x.spend, i.spend),                          on=.(spend, week > week)] breakdown$calc3 <- with(breakdown, x.salesx*(uplift*(1-exp(-(carryover^(x.week-week)/slope))))/spend)  breakdown <- breakdown[,                    .(calc3 = sum(calc3)),                    = .(week, spend)][                      is.na(calc3), calc3 := 0.0][] breakdown <- breakdown %>% arrange(spend, week) 

to avoid reading join memory, i’m looking solution this:

  breakdown2 <-      setdt(tempdata)[       tempdata, on = .(spend, week > week),        .(calc3 = sum(x.salesx*(uplift*(1.0-exp(-(carryover^(x.week-week)/slope))))/spend)),        = .eachi][         is.na(calc3), calc3 := 0.0][] 

however, output (breakdown2) inconsistent output of ‘breakdown’. have tried switching call = .(week, spend) , various other permutations (including rearranging data) no avail. i’m little confused how prefixes each of joined variables assigned , how function works. appreciated!