topic Re: How to convert rows into columns ? in Talend Studio

How to convert rows into columns ?

Anonymous — Tue, 20 May 2014 08:28:33 GMT

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.

Re: How to convert rows into columns ?

Anonymous — Tue, 20 May 2014 09:08:05 GMT

may not be optimized but I have a small hint...
- Read xml file
- Merge three columns into Picture1 with separator as "|" using tMap/javarow and string concatenations
- Use tNormalize for Picture1 column
- Use tJavaRow - create new column as rowcnt - Identify row count where id is 1
- use filter component to filter out where rowcnt > 3
Vaibhav

Re: How to convert rows into columns ?

Anonymous — Tue, 20 May 2014 09:15:45 GMT

Hi SPITman,
It could be possible with TOS but the output you expected is not clear.
I got the output in trasnposed format with the input file as you mentioned.
i have used 3 outputs in tmap and applied inner join between keys for each picture type(1,2,3).
output:
.--+-----------.
| tLogRow_1 |
|=-+----------=|
|id|picture1 |
|=-+----------=|
|1 |7355448.jpg|
|1 |7893544.jpg|
|1 |2364758.jpg|
|2 |1234567.jpg|
|2 |7864125.jpg|
|2 |1478523.jpg|
|3 |7391845.jpg|
|3 |7319846.jpg|
|3 |7829657.jpg|
'--+-----------'
Thanks,
Bhanu.

Re: How to convert rows into columns ?

Anonymous — Tue, 20 May 2014 09:30:00 GMT

Hi,
In fact, 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

In the input XML file, I can have several pictures (25 for instance) for one id.
In the output Excel, I would like to obtain only the first three pictures (COLUMNS and not ROWS)

Re: How to convert rows into columns ?

Anonymous — Tue, 20 May 2014 09:36:59 GMT

Hi,
I think you don't understand me...

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

In the input XML file, I can have several pictures (25 for instance) for one id.
In the output Excel, I would like to obtain only the first three pictures (in COLUMNS and not ROWS)
That's all

Re: How to convert rows into columns ?

Anonymous — Tue, 20 May 2014 11:29:06 GMT

Hi,
I hope tSplitRow will solve the your issue.
Stick with this URL: http://www.vikramtakkar.com/2013/09/tra ? split.html
Thanks,
kumar

Re: How to convert rows into columns ?

Anonymous — Tue, 20 May 2014 12:34:50 GMT

Hi,
tSplitrow splits a row into several rows. I search to split rows into several columns.

Re: How to convert rows into columns ?

Anonymous — Wed, 21 May 2014 10:20:18 GMT

I have created a sample job and tested, please check whether it is as expected or not.

Re: How to convert rows into columns ?

Anonymous — Wed, 21 May 2014 10:44:12 GMT

Thanks for your reply.
I check it now !

Re: How to convert rows into columns ?

Anonymous — Wed, 21 May 2014 10:50:39 GMT

if you want to obtain first three images then you have to used tDenormalizeSortedRow component instead tDenormalize. rest of the things remain same.

Re: How to convert rows into columns ?

Anonymous — Wed, 21 May 2014 10:54:09 GMT

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

Re: How to convert rows into columns ?

Anonymous — Wed, 21 May 2014 11:06:52 GMT

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.

Re: How to convert rows into columns ?

Anonymous — Wed, 21 May 2014 12:24:35 GMT

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 ?

Re: How to convert rows into columns ?

Anonymous — Wed, 21 May 2014 12:38:29 GMT

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

Re: How to convert rows into columns ?

Anonymous — Wed, 21 May 2014 12:52:51 GMT

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.

Re: How to convert rows into columns ?

Anonymous — Wed, 21 May 2014 13:52:36 GMT

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

Re: How to convert rows into columns ?

Anonymous — Wed, 21 May 2014 19:07:53 GMT

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...

Re: How to convert rows into columns ?

Anonymous — Wed, 21 May 2014 19:31:58 GMT

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

Re: How to convert rows into columns ?

Anonymous — Thu, 22 May 2014 07:30:42 GMT

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|
'--+------------------------------------------------------------------+-----------+-----------'

Re: How to convert rows into columns ?

Anonymous — Thu, 22 May 2014 08:12:57 GMT

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