title: R: Joining Tibbles Using Derived Column Values
body: <p>Consider the following tibbles:</p>
  <pre><code>library(tidyverse)
  
  tbl_base_ids = tibble(base_id = c(&quot;ABC&quot;, &quot;ABCDEF&quot;, &quot;ABCDEFGHI&quot;), base_id_length = c(3, 6, 9), record_id_length = c(10, 12, 15))
  tbl_records  = tibble(record_id = c(&quot;ABC1234567&quot;, &quot;ABCDEF123456&quot;, &quot;ABCDEFGHI123456&quot;))
  
  </code></pre>
  <p>I'd like to join matching rows to produce a third tibble:</p>
  <pre><code>tbl_records_with_base 
  
    record_id
    base_id
    base_id_length
    record_id_length
  </code></pre>
  <p>As you can see, this is not a matter of joining one or more variables from each of the first two. This requires matching variable derivatives. In SQL, I'd do this:</p>
  <pre><code>  SELECT A.record_id, 
      B.base_id, 
      B.base_id_length, 
      B.record_id_length
    FROM tbl_records A 
      JOIN tbl_base_ids B 
        ON LENGTH(a.record_id) = B.record_id_length
          AND LEFT(a.record_id, B.base_id_length) = B.base_id
  </code></pre>
  <p>I've tried variations of dplyr joins and using the match function, to but to no avail. Can someone help? Thank you.</p>