Pentaho Kettle - How to update null values for sorted rows in kettle

Question

I try to transform data from table 1 to table 2 via kettle. The situation is, in table 1 the data is display like this:

  c1       c2
  -------  ----------  
  A        1     
  A        null
  A        null
  B        2
  B        null
  B        null

And I want the steps to group values in column 1 by A, B..., and replace the null values in column 2. So the output will be like:

c1       c2
-------  ----------  
A        1     
A        1
A        1
B        2
B        2
B        2

I've tried to use the step called Database lookup to replace all the null values, but apparently it can't group values in column 1 first and then replace based on column 2.

I know with sql script can do this work, but can some steps in spoon do that as well? Any help/suggestion will be really appreciated.

score 0 · Answer 1 · answered Apr 15 '19 at 13:01

Some input steps have a 'repeat' function, but there isn't any step for the workflow ready with this function, so i use this javascript code that does that.

var row, last_row;

if (row != null) {
    //Save the data of the current row.
    last_row = row;

}else {
    //swap null value with last non-null value.
    row = last_row;

}

You just have to change the name 'row' for the desired column you wish to repeat. In your example it would be c2, and last_c2.

Pentaho Kettle - How to update null values for sorted rows in kettle

1 Answers1