title: Cartesian rolling join with data.table
body: <p>I'm not exactly sure how to describe this, but I'll gladly edit the title and/or post to reflect comments and answers.</p>
  
  <p><strong>Problem</strong></p>
  
  <p>I have two <code>data.frames</code> that I would like to merge with a combination of a left join, an outer join, and a rolling join. </p>
  
  <ul>
  <li>One of the key columns (<code>year</code>) is for the rolling join.</li>
  <li>Another key column (<code>cat</code>) is common to both <code>data.frames</code>. In the example below I've only supplied exemplary subsets of the full data, which has thousands of values for <code>cat</code>. </li>
  <li>The first <code>data.frame</code>, <code>X</code>, has an additional key column <code>cnty</code> (county), and the second <code>data.frame</code>, <code>Y</code>, has an additional key column <code>pol</code> (pollutant). </li>
  <li>For each group defined by <code>cat</code> and <code>year</code>, I would like the final result to contain a cartesian product of <code>cnty</code> and <code>pol</code>, with value columns <code>emfac</code> (from <code>X</code>) and <code>tput</code> (from <code>Y</code>). The goal is to be able to compute <code>emfac * tput</code>.</li>
  </ul>
  
  <p>Here is an exemplary subset of <code>X</code>:</p>
  
  <pre><code>  cat year cnty  tput
  1  29 2011  ALA 67852
  2  29 2011   CC 33893
  3  29 2011  MRN 11319
  </code></pre>
  
  <p>... and here is an exemplary subset of <code>Y</code>:</p>
  
  <pre><code>  cat year pol emfac
  1  29 1975 TOG   2.4
  2  29 1975  PM   5.3
  </code></pre>
  
  <p><strong>Closest attempt so far</strong></p>
  
  <p>I can almost, but not quite, get the output I want:</p>
  
  <pre><code>X &lt;- structure(list(
         cat = c(29L, 29L, 29L), 
         year = c(2011L, 2011L, 2011L), 
         cnty = c("ALA", "CC", "MRN"), 
         tput = c(67852, 33893, 11319)), 
         .Names = c("cat", "year", "cnty", "tput"), 
         class = c("data.frame"), row.names = c(NA, -3L))
  
  Y &lt;- structure(list(
         cat = c(29L, 29L), 
         year = c(1975, 1975), 
         pol = c("PM", "TOG"), 
         emfac = c(2.4, 5.3)),
         .Names = c("cat", "year", "pol", "emfac"), 
         class = c("data.frame"), row.names = c(NA, -2L))
  
  library(data.table)
  X &lt;- data.table(X, key = c("cat", "cnty", "year"))
  Y &lt;- data.table(Y, key = c("cat", "pol", "year"))
  Y[X, roll = TRUE]
  
     cat year pol emfac cnty  tput
  1:  29 2011  PM   5.3  ALA 67852
  2:  29 2011  PM   5.3   CC 33893
  3:  29 2011  PM   5.3  MRN 11319
  </code></pre>
  
  <p>This is my "nearest miss". Most of my other attempts are much more wrong.</p>
  
  <p><strong>Expected result</strong></p>
  
  <pre><code>   cat year pol emfac cnty  tput
  1:  29 2011  PM   5.3  ALA 67852
  2:  29 2011  PM   5.3   CC 33893
  3:  29 2011  PM   5.3  MRN 11319
  4:  29 2011 TOG   2.4  ALA 67852
  5:  29 2011 TOG   2.4   CC 33893
  6:  29 2011 TOG   2.4  MRN 11319
  </code></pre>
  
  <p><strong>What am I doing wrong?</strong></p>