CODEWITHSUNDEEP
pentaho-spoonpentaho-data-integration
Daniel Möller
Daniel Möller

Reputation: 86600

Pentaho data integration - Separate fields into rows

I have a table (loaded from an CSV file) in Pentaho with many fields that I would like to transform into rows.

Imagine I have the following fields:

Name, City, Jan, Fev, Mar, Apr, ...., Dec, Total
   10 records

Where the fields from Jan to Dec are numeric values. I need get each of the month fields and separate them into rows, in a new table like:

Name, City, Month, Value
   10 * 12 records

I am currently using the "Select values" transformation to create 12 individual tables and then add their rows into a single table.

Isn't there any transformation that allows me to do this in an easier way?

Upvotes: 0

Views: 724

Answers (1)

Daniel Möller
Daniel Möller

Reputation: 86600

There is a transform called "Row Normaliser" which does exactly this.

Add it to the graph and:

  • in the "Type field", you write "Month" (this will be a new field that will receive the values in the "type" column below)
  • in the "Fieldname" column, select all fields that are months in the original table
  • in the "type" column, write the name of the months as they should appear in the "Month" field of the result table
  • in the "new field" column, write the name of the field that will receive the values that are currently in the month columns. Example: "Monthly_Value".

Upvotes: 1

Related Questions