kettlepentaho-spoon

Pentaho: How to dynamically modify rows?

I have a table with population counts over the last 25 years approximately. Of different towns and cities. I also have a table with additional data about most of each of those cities, so I performed a left outer join (with population counts as the left table and extra data as the right table) to append the extra data to all of the rows of the left table.

There are 2 towns that I didn't have info for on the right table, so as expected they got added all the fields with nulls. Since it's only about 10 rows, I'd like to manually modify those to add the missing information. However, I haven't been able to find a transformatiom to be able to do this properly.

I've tried using "Modified JavaScript value" with the following code a an example:

if(Municipios == "36011 Cerdedo"){
 COD_INE = "36011000000";
}
else if(Municipios == "36012 Cotobade"){
 COD_INE = "36012000000";
}

What I wanted to try with this is to set the value of the field 'COD_INE' depending on the value of the field 'Municipios', but I get the error 'the field Municipios is not defined'.

I have also tried the Value Mapper, but that wouldn't help since the rows I need to fill have different data each.

Another possibily that would fix my issue is to add rows to the right table so that after the join every row is complete, so if there's a way of doing that it would also work. I tried using a Row Generator with the exact same fields as the input data then Merge Join, but since it considers the Row Generator and the input data 2 different tables, it duplicates all the fields.

The only restriction that I have is that I can't modify the input data (I know it would be the easiest and fastest solution but I'm not allowed to do that for this college project since the idea is to learn how to use Pentaho Spoon).

Solution

  • The reason why you're getting the error the field Municipios is not defined is because the Field is not found in the input stream.

    Make sure it is spelled correctly (it is case sensitive), you can check available Fields on the left panel in 'Input Fields' section and double click to add it to your code.

    To update the Field COD_INE you need add the field name in the Fields section at the bottom and set Replace value to Y to overwrite the existing field on matching value. You set as N if the field does'nt exist.

    Javascript Step