title: How to best join one column of a data.table with another column of the same data.table?
body: <h2>My data</h2>
  
  <p>I have a data.table <code>DT</code> with the current (<code>F0YR</code>) and the next (<code>F1YR</code>) fiscal year-end (FYE) encoded as integers. Since every next FYE will eventually become
  a current FYE, the integer will be both in the column <code>F1YR</code> and <code>F0YR</code>. Also, my data contains monthly observations so the same FYE will be in the data set
  multiple times:</p>
  
  <pre><code>library(data.table)
  DT &lt;- data.table(ID     = rep(c("A", "B"), each=9),
                   MONTH  = rep(100L:108L, times=2),
                   F0YR   = rep(c(1L, 4L, 7L), each=3, times=2),
                   F1YR   = rep(c(4L, 7L, 9L), each=3, times=2),
                   value  = c(rep(1:5, each=3), 6, 6, 7),
                   key    = "ID,F0YR")
  DT
        ID MONTH F0YR F1YR value
   [1,]  A   100    1    4     1
   [2,]  A   101    1    4     1
   [3,]  A   102    1    4     1
   [4,]  A   103    4    7     2
   [5,]  A   104    4    7     2
   [6,]  A   105    4    7     2
   [7,]  A   106    7    9     3
   [8,]  A   107    7    9     3
   [9,]  A   108    7    9     3
  [10,]  B   100    1    4     4
  [11,]  B   101    1    4     4
  ...
  </code></pre>
  
  <h2>What I want to do</h2>
  
  <p>For every <code>ID</code> and <code>F1YR</code> combination, I want to get the value for the <code>ID</code> and <code>F0YR</code> combination. As an example: Company A had a value of <code>2</code> for <code>FOYR==4</code>. Now, 
  I want an additional column for all combinations with <code>ID=="A"</code> and <code>F1YR==4</code> which is set to 2, next to the already existent value of 1.</p>
  
  <h2>What I tried</h2>
  
  <pre><code>intDT &lt;- DT[CJ(unique(ID), unique(F0YR)), list(ID, F0YR, valueNew = value), mult="last"]
  setkey(intDT, ID, F0YR)
  setkey(DT, ID, F1YR)
  DT &lt;- intDT[DT]
  setnames(DT, c("F0YR.1", "F0YR"), c("F0YR", "F1YR"))
  DT
        ID F1YR valueNew MONTH F0YR value
   [1,]  A    4        2   100    1     1
   [2,]  A    4        2   101    1     1
   [3,]  A    4        2   102    1     1
   [4,]  A    7        3   103    4     2
   [5,]  A    7        3   104    4     2
   [6,]  A    7        3   105    4     2
   [7,]  A    9       NA   106    7     3
   [8,]  A    9       NA   107    7     3
   [9,]  A    9       NA   108    7     3
  [10,]  B    4        5   100    1     4
  [11,]  B    4        5   101    1     4
  ...
  </code></pre>
  
  <p>(Note that I use <code>mult="last"</code> here because, although the values should only change with F0YR or F1YR changes, sometimes they don't and this is just my
  tie breaker).</p>
  
  <h2>What I want</h2>
  
  <p>This looks improvable. First of all, I have to make a copy of my DT. Second, since I join basically the same <code>data.table</code>, all the column names have the same name
  and I have to rename them. I thought that a <code>self join</code> would be the way forward, but I tried and tried and couldn't get a nice solution. I have the hope
  that there is something easy out there which I just don't see...Does anyone have a clue? Or is my data set up in such a way that it is actually hard
  (maybe because I have monthly observations, but want to join only quarterly or yearly changing values).</p>