How to convert rows into columns ?

[Anonymous]

Hi,
I have an input XML file.
I would like to obtain the following result :
id | picture1 | picture2 | picture3
1 | 7355448.jpg | 7893544.jpg | 2364758.jpg
2 | 1234567.jpg | 7864125.jpg | 1478523.jpg
3 | 7391845.jpg | 7319846.jpg | 7829657.jpg
...
For one id, i can have one or several pictures.
In the output Excel, I would like to obtain only the first three pictures.
When I realize the mapping, I get the following result :
id | picture1
1| 7355448.jpg
1| 7893544.jpg
1| 2364758.jpg
1| 7365599.jpg
1| 7733548.jpg
2| 1234567.jpg
2| 7864125.jpg
2| 1478523.jpg
2| 1122778.jpg
2| 9977443.jpg
...
Can I do that with TOS ? How to solve the problem ?
Regards.

[Anonymous]

Hi,
I have an error :
Exception in component tMap_1
java.lang.ArrayIndexOutOfBoundsException: 1

[Anonymous]

check below options.
1> your input string has value
2> string has more than three values for all the rows.
3> validate after split for values.

[Anonymous]

check below options.
1> your input string has value : YES
2> string has more than three values for all the rows : NO because several rows can be empty : several ID can not have pictures...
3> validate after split for values : YES ?

[Anonymous]

I can have empty values for the position , and .
That's why I have an error on the position : java.lang.ArrayIndexOutOfBoundsException: 1

[Anonymous]

have you tested that you have values for position 1 and 2, because function is same only parameters are different. i am suspecting that second value must be empty or null? check once. if not then print input column values and check you are getting right result or not.

[Anonymous]

use below sample code in your tMap to resolve above issue. you can extend this code as per your requirement.

for first column: row6.picture1!=null? row6.picture1.split(";").length >0?row6.picture1.split(";"):null
:null
for second column: row6.picture1!=null? row6.picture1.split(";").length >=1?row6.picture1.split(";"):null
:null
for third column: row6.picture1!=null? row6.picture1.split(";").length >=2?row6.picture1.split(";"):null
:null

[Anonymous]

Thanks for your reply guy but it doesn't work...
I always have the same error : java.lang.ArrayIndexOutOfBoundsException: 1
You're right my second value is empty, that's why I have the error. However, I check it...

[Anonymous]

After the tDenormalize, connect it to a tExtractDelimitedFields with the target schema as it's output schema and the "Field to split" set to that second column and the "Field separator" to the same value as "Delimiter" from the tDenormalize. It should ignore it in the event that there aren't enough of them.
When I run the job in the attached screenshot, I get the following output:

 connecting to socket on port 3480
 connected
.--+--------------.
|    tLogRow_2    |
|=-+-------------=|
|Id|Picture       |
|=-+-------------=|
|1 | 7355448.jpg  |
|1 | 7893544.jpg  |
|1 | 2364758.jpg  |
|1 | 7365599.jpg  |
|1 | 7733548.jpg  |
|2 | 1234567.jpg  |
|2 | 7864125.jpg  |
|2 | 1478523.jpg  |
|2 | 1122778.jpg  |
|2 | 9977443.jpg  |
'--+--------------'
.--+------------+------------+------------.
|                tLogRow_1                |
|=-+------------+------------+-----------=|
|Id|Picture_1   |Picture_2   |Picture_3   |
|=-+------------+------------+-----------=|
|1 | 7355448.jpg| 7893544.jpg| 2364758.jpg|
|2 | 1234567.jpg| 7864125.jpg| 1478523.jpg|
'--+------------+------------+------------'
 disconnected

[Anonymous]

this is another good way of getting values, but when i have implemented your job i am getting below output, am i missing something?

.--+------------------------------------------------------------------+-----------+-----------.
|                                          tLogRow_2                                          |
|=-+------------------------------------------------------------------+-----------+----------=|
|id|picture1                                                          |picture2   |picture3   |
|=-+------------------------------------------------------------------+-----------+----------=|
|1 | 7355448.jpg; 7893544.jpg; 2364758.jpg; 7365599.jpg; 7733548.jpg  |7355448.jpg|7893544.jpg|
|2 | 1234567.jpg; 7864125.jpg; 1478523.jpg; 1122778.jpg; 9977443.jpg  |1234567.jpg|7864125.jpg|
'--+------------------------------------------------------------------+-----------+-----------'

[Anonymous]

Hi,
Yes... In the column picture1, I want to have only one picture (as you can see in columns picture2 and picture3)