title: Dynamically execute a transformation against a column at runtime
body: <p>I have a Pentaho Kettle job that can load data from x number of tables, and put it into target tables with a different schema.</p>
  
  <p>Assume I have table 1, like so:</p>
  
  <p><a href="https://i.stack.imgur.com/eco2d.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eco2d.png" alt="An example source data table"></a></p>
  
  <p>I want to load this table into a destination table that looks like this:
  <a href="https://i.stack.imgur.com/CsnLX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CsnLX.png" alt="An example destination data table"></a></p>
  
  <p>The columns have been renamed, the order has been changed, and the data has been transformed.  The rename, and order is easily managed by using the Select Values step, which can be used within an ETL Metadata Injection step, making it dependent on some configuration values loaded at runtime.</p>
  
  <p>But if I need to perform some transformation logic on some of the columns, based on where they go in the target table, this seems to be less straightforward.</p>
  
  <p>In my example, I want the column "<strong>CountryName</strong>" to be capitalised, and the column "<strong>Rating</strong>" to be floored (as in changing the real number to the previous integer value).</p>
  
  <p>While I could do this by just manually adding a transformation to accomplish each, I want my solution to be dynamic, so it could just as easily run the "<strong>CountryName</strong>" column through a checksum component, or perform a ceiling on "<strong>Rating</strong>" instead.</p>
  
  <p>I can easily wrap these transformations in another transformation so that they can be parameterised and executed when needed:
  <a href="https://i.stack.imgur.com/jsdVk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jsdVk.png" alt="An example of a transformation wrapper"></a></p>
  
  <p>But, where I'm having trouble is, when I process a row of data, I need a way to be able to say:</p>
  
  <ul>
  <li>Column "<strong>CountryName</strong>" should be passed through the Capitalisation transform</li>
  <li>Column "<strong>Rating</strong>" should be passed through the Floor transform</li>
  <li>Column(s) "<strong>AnythingElse</strong>" should be passed through the SomeOther transform</li>
  </ul>
  
  <p>Is there a way to dynamically split out the columns in a row, and execute a different transform on each one, based on some configuration metadata that can be supplied?</p>
  
  <p>Logically, it would be something like this, although I suspect there may be a way to handle it as a loop or some form of dynamic transformation, rather than mapping out a path per column:
  <a href="https://i.stack.imgur.com/zIjSb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zIjSb.png" alt="Logical diagram of the process - each column going to a different transform"></a></p>
  
  <p>Kettle is so flexible that it seems like there must be a way to do this, I'm just struggling to know which components to use and how to do it.  Any experts out there have some suggestions?</p>
  
  <p>I'm dealing with some biggish data sets here (hundreds of millions of rows) so reluctant to use Row Normaliser/Denormaliser or writing to file/DB if possible.</p>