Resolved: Return first matched row using date lookup to a date range lookup table

## This is my base data<br />
df1 <- 
structure(list(Date = structure(c(18848, 18848, 18849, 18890, 
18892, 18901, 18904, 18904, 18906, 18906, 18907, 18911, 18911, 
18922, 18923, 18925, 18926, 18927, 18929), class = "Date"), Id = c(4, 
6, 4, 6, 4, 4, 4, 6, 4, 6, 4, 4, 6, 4, 4, 4, 4, 6, 6)), row.names = c(NA, 
19L), class = "data.frame")

## This is my date range table
df2 <- structure(list(Date.Start = structure(c(18898, 18897, 18848, 
18898, 18897), class = "Date"), Date.End = structure(c(18924, 
18924, 18903, 18924, 18924), class = "Date"), Id = c(6, 6, 4, 
4, 4), Return.Value = c(1, 2, 3, 4, 5)), row.names = c(NA, 5L
), class = "data.frame")
[/code]

</p>



<a href="https://i.stack.imgur.com/W9zp1.png" rel="nofollow noreferrer"><img decoding="async" src="https://i.stack.imgur.com/W9zp1.png" alt="enter image description here"></a><br> 



So for the first row of <code>df1</code>, Date = '2021-08-09' sits within date range '2021-08-09' to '2021-10-03' (row 3 of <code>df2</code>) so I want to add a <code>Return Value</code> column to <code>df1</code> with a value of <code>3</code>.
There is no match for row 2 in <code>df1</code> so this will return nothing.<br> 



My expected output is:<br> 



<a href="https://i.stack.imgur.com/M4rSA.png" rel="nofollow noreferrer"><img decoding="async" src="https://i.stack.imgur.com/M4rSA.png" alt="enter image description here"></a><br> 



I tried this link <a href="https://stackoverflow.com/questions/52793388/check-if-a-date-is-in-range-of-lookup-table">Check if a date is in range of lookup table</a> but I want to add a column from <code>df2</code> instead of returning logical vector.<br> 



<h2 class="wp-block-heading">Answer:</h2>



<em>data.table</em> version using the <code>mult="first"</code> option on a non-equi join allowing <code>>=</code> and <code><=</code> comparisons:<br> 


<p>[code]library(data.table)<br />
setDT(df1)<br />
setDT(df2)<br />
df1[, Return.Value := df2[<br />
    df1, on=c("Id", "Date.Start<=Date", "Date.End>=Date"), Return.Value, mult="first"]]<br />
df1<br />
#          Date Id Return.Value<br />
# 1: 2021-08-09  4            3<br />
# 2: 2021-08-09  6           NA<br />
# 3: 2021-08-10  4            3<br />
# 4: 2021-09-20  6           NA<br />
# 5: 2021-09-22  4            3<br />
# 6: 2021-10-01  4            3<br />
# 7: 2021-10-04  4            4<br />
# 8: 2021-10-04  6            1<br />
# 9: 2021-10-06  4            4<br />
#10: 2021-10-06  6            1<br />
#11: 2021-10-07  4            4<br />
#12: 2021-10-11  4            4<br />
#13: 2021-10-11  6            1<br />
#14: 2021-10-22  4            4<br />
#15: 2021-10-23  4            4<br />
#16: 2021-10-25  4           NA<br />
#17: 2021-10-26  4           NA<br />
#18: 2021-10-27  6           NA<br />
#19: 2021-10-29  6           NA