I have to do a process with Pentaho spoon and I'm stuck. I think it has to be something simple and I hope you can help me.
I start from a table that has some multivalued fields that are separated by a comma. What I want is to create a table with the ID field and separate the multi-valued in the corresponding rows, inserting new ones but keeping the ID field. And then have a weight field where 1 is divided by the number of rows each ID has.
It would be something like this:
ID TYPE MATERIAL PERSON
1 2444 MATERM0140 A,B
2 2444,1555 WATER00000114 C
3 2444 WATER00000016 D
4 2444 WATER00000970 D
5 2444 MATERM0180 C
6 2444,1555 WATER00000490 A,E
7 2444,1555 WATER00000645 E
8 2444 WATER00000900 B
And I would have the resulting tables:
ID PERSON PONDERACION
1 A 0,5
1 B 0,5
2 C 1
3 D 1
4 D 1
5 C 1
6 A 0,5
6 E 0,5
7 E 1
8 B 1
ID TYPE PONDERACION
1 2444 1
2 2444 0,5
2 1555 0,5
3 2444 1
4 2444 1
5 2444 1
6 2444 0,5
6 1555 0,5
7 2444 0,5
7 1555 0,5
8 2444 1
Can you help me out with this? Thank you!! ^^