1

I have a problem use Kettle/PDI. I need did columns to separate specifics values. But I need to create new lines with just this values, one row for each column. I tried to use row normaliser but it's didn't work. Please can someone help me?

Thanks in advance.

e.g. I have data like this:

I have data like this:

n1 | row 1 | value 1.1 | ... | null    | null
n1 | row 2 | value 2.1 | ... | null    | null
n1 | row 3 | value 3.1 | ... | null    | null
n1 | row 4 | value 4.1 | ... | 1200,00 | 1500,00
n2 | row 1 | value 1.1 | ... | null    | null
n2 | row 2 | value 2.1 | ... | null    | null
n2 | row 3 | value 3.1 | ... | null    | null
n2 | row 4 | value 4.1 | ... | 1120,00 | 1350,00

I would like this:

n1 | row 1 | value 1.1 | ... | null    | null
n1 | row 2 | value 2.1 | ... | null    | null
n1 | row 3 | value 3.1 | ... | null    | null
n1 | row 4 | value 4.1 | ... | 1200,00 | 1500,00
n1 | row 5 | 1200,00   | ... | null    | null
n1 | row 6 | 1500,00   | ... | null    | null
n2 | row 1 | value 1.1 | ... | null    | null
n2 | row 2 | value 2.1 | ... | null    | null
n2 | row 3 | value 3.1 | ... | null    | null
n2 | row 4 | value 4.1 | ... | 1120,00 | 1350,00
n2 | row 5 | 1120,00   | ... | null    | null
n2 | row 6 | 1350,00   | ... | null    | null
thiagofred
  • 197
  • 10
  • An example of how your data looks at the start of the transformation and how you want it to look when it's done would be very helpful. – Brian.D.Myers Mar 21 '16 at 18:54
  • Sorry, i tried post pics but I can't. I will try to explain. – thiagofred Mar 21 '16 at 18:56
  • do you really need a row counter which says `row `? Your output looks a bit strange, and complicates things a lot. Also your input is missing headers. – bolav Mar 22 '16 at 09:58
  • So @bolav, I don't need the row it's just to understand the behavior. And I didn't show the headers in this examples, I showed only the fields. But the operation is like this, because I have more of 10 columns in my output. – thiagofred Mar 22 '16 at 11:15
  • Your problem is probably solvable easier with row normaliser, depending on your requirements. – bolav Mar 22 '16 at 13:18
  • I couldn't solve my problem with row normaliser, I have multiple excel inputs with 100 rows each, then I need to do this change for each input that was grouped. – thiagofred Mar 22 '16 at 15:44
  • Then I guess you could do a combination of `ETL Metadata Injection` and `Row Normaliser`. – bolav Mar 23 '16 at 22:18
  • So I did so easy, after this explanation, filtering, cloning the necessary rows and using a javascript step, then just merge and sort the rows. – thiagofred Mar 31 '16 at 15:48

1 Answers1

1

If you want to clone the rows which have a value in a field, this is a way to do that part of the task.

Screenshot

Check if the value exists, then clone it. If it is the clone move the value to the main value, and set the fields that we want to clear to null. Remove the cloned field, and merge the streams again.

bolav
  • 6,938
  • 2
  • 18
  • 42