title: Find first non-null value scanning rows backwards from current row
body: <h2>Software</h2>
  
  <p>I'm using Pentaho Data Integration 5.4</p>
  
  <h2>Input data & explanation</h2>
  
  <p>Input data from a file (simplified, there are more columns):</p>
  
  <pre><code>number       name
  1009       ProductA
  2150       ProductB
  3235       ProductC
             ProductD
             ProductE
  1234       ProductF
  7765       ProductG
  4566       ProductH
             ProductI
  9907       ProductJ
  </code></pre>
  
  <p>The issue is that I had an <code>Excel</code> file format <code>xlsx</code> which has the data with merged cells, and for one value of <code>id</code> there are <code>1..n</code> rows of values.</p>
  
  <p>After converting that file to <code>csv</code> values for next rows (other than first) are missing, despite the one column which was not merged (see example <code>id=3</code>, <code>id=6</code>).</p>
  
  <p>I'm generating a <code>sequence</code> using step <code>Add sequence</code>, the input is sorted the way it was originally stored in a file.</p>
  
  <h2>Steps to achieve the goal</h2>
  
  <p>Basically what I need to do is:</p>
  
  <ul>
  <li>Find first non-null value that has <code>sequence_number</code> less than <code>current_row.sequence_number</code></li>
  <li>Concatenate the value from field <code>name</code> to that matching row</li>
  <li>Keep scanning next rows with <code>sequence_number</code> higher than the last scanned</li>
  </ul>
  
  <p>As stated before, there can be <code>1..n</code> rows of values for such case.</p>
  
  <h2>Expected output</h2>
  
  <pre><code>number       name
  1009       ProductA
  2150       ProductB
  3235       ProductC; ProductD; ProductE
  1234       ProductF
  7765       ProductG
  4566       ProductH; ProductI
  9907       ProductJ
  </code></pre>
  
  <h2>My approach</h2>
  
  <p>I believe I'm able to do this in a loop, by using <code>Analytic Query</code> and calculating <code>LAG(1)</code> and then concatenating the column <code>name</code> for one row with null values and discarding other column values from null row - and then doing this in a loop (for like 20 times assuming this is maximum), but I do consider this a bad idea.</p>
  
  <p>There are probably better ways to achieve this result using for example <code>Java Script</code> step with scanning the rows backward from current (based on <code>sequence</code> number), but I'm unaware of those functions, if they do exist.</p>
  
  <p>How can I achieve this using <code>Modified Java Script Value</code> step, or any other efficient way without using a loop for entire content of the file until there are no empty rows?</p>