6

I'm new to Pentaho and I need to transpose a table from rows to columns, but the first column doesn't contain the headers.

It looks something like this:

Jan/15  Feb/15  Mar/15  Apr/15
1.1     3.4     1.7     2.0
2.5     4.5     2.4     3.3

And I need to convert to a table like the on below:

Month   Value A  Value B
Jan/15  1.1      2.5
Feb/15  3.4      4.5
Mar/15  1.7      2.4
Apr/15  2.0      3.3

Someone knows how can I achieve this? Is there some step that could help or only through scripts?

Slaski
  • 65
  • 1
  • 1
  • 5

1 Answers1

8

You have to add the value name (A and B) to the row. And then first do a Row Normaliser, to make rows. Then you have to Row denormaliser, to get the two different values on the same row.

enter image description here

enter image description here

enter image description here

Convert string date, to date for correct sort: Screenshot of select values

enter image description here

enter image description here

enter image description here

bolav
  • 6,938
  • 2
  • 18
  • 42
  • Thank you very much, I did it here and solved it! =] – Slaski Mar 07 '16 at 17:45
  • What about the other way around?I have a data set where as columns I do have Months, fieldA, fieldB, FieldB,FieldC...Field_N and I want the transpose to be Months as column and as rows all those fields, in the same excel sheet. I tried with same component, I got the transpose, but problem is to get differents months value as header for the columns. Either I missing something on how to use the denormaliser and normaliser or this specific task is not possible just with kettle and I should think of other solutions – Carmine Tambascia Mar 04 '18 at 20:50