title: data.table in R: Count if with multiple criteria between 2 data.tables
body: <p><strong>Story</strong></p>
  
  <p>I am trying to add information about count of changes done on vendor (stored in Table 2) to my main set of data (stored in Table 1). </p>
  
  <p>I am finding it difficult to connect counting of T2 elements with creation of column in T1.</p>
  
  <p><strong>Example</strong></p>
  
  <p>Example of counting for single vendor and specified field name might look like this and works properly: </p>
  
  <pre><code>nrow(T2[Vendor == 101337 &amp; Field.Name == "Deletion flag" &amp; Date &gt;= 42403 &amp; Date &lt;= 42527]
  </code></pre>
  
  <p><strong>Issue</strong></p>
  
  <p>What I find tricky is how to pass Vendor id from T1 during nrow command when I am creating new column in T1. Logically I am trying to create new column that consist of above command but instead of specified vendor (and dates) I am trying to use Vendor from T1.</p>
  
  <pre><code>T1[, Deletion_Flag_N := nrow(T2[Vendor == T1$Vendor &amp; Field.Name == "Deletion flag" &amp; Date &gt;= T1$Doc..Date &amp; Date &lt;= T1$Clearing])]
  </code></pre>
  
  <p>Of course it is not working. Any idea?</p>
  
  <p><strong>Alternative</strong></p>
  
  <p>I am also thinking about separate temp table where I will have vendor, date and count of specific changes done on that period of time. Then I have a question how to make join based not only on vendor but also on date (it has to be between value of 2 other fields from T1)</p>
  
  <pre><code>    T2[Field.Name == "Deletion flag"][, .(Vendor, Date)][, .N ,by = list(Vendor, Date)]
  
     Vendor  Date N
  1: 106724 42492 2
  2: 105172 42537 1
  3: 101215 42432 1
  </code></pre>
  
  <p><em>Thank you for any suggestions!</em></p>
  
  <p><strong>Data used:</strong></p>
  
  <p>Table 1</p>
  
  <pre><code>&gt; head(T1[, .(ID, Vendor, Doc..Date, Clearing)])
                      ID Vendor Doc..Date Clearing
  1: 1900000276_107_2016 107393     42403    42527
  2: 1900000569_107_2016 107393     42494    42527
  3: 1700000117_107_2016 107394     42499    42499
  4: 1700000120_107_2016 107394     42397    42517
  5: 1900000442_107_2016 107394     42397    42517
  6: 1900000501_107_2016 107607     42474    42513
  </code></pre>
  
  <p>Table 2</p>
  
  <pre><code>&gt; head(T2[, .(Vendor, Field.Name, Date, New.value)])
     Vendor Field.Name  Date New.value
  1: 101337 Payt Terms 42626      45DN
  2: 101337 Payt Terms 42626      45DN
  3: 136370 Payt Terms 42611      60DN
  4: 107047 Payt Terms 42580      90DN
  5: 101337 Payt Terms 42569      45DN
  6: 136370 Payt Terms 42558      60DN
  </code></pre>