One Star

How to convert rows into columns ?

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.
24 REPLIES
Four Stars

Re: How to convert rows into columns ?

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
One Star

Re: How to convert rows into columns ?

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

Re: How to convert rows into columns ?

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)
One Star

Re: How to convert rows into columns ?

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
One Star

Re: How to convert rows into columns ?

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

Re: How to convert rows into columns ?

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

Re: How to convert rows into columns ?

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

Re: How to convert rows into columns ?

Thanks for your reply.
I check it now !
Five Stars

Re: How to convert rows into columns ?

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

Re: How to convert rows into columns ?

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

Re: How to convert rows into columns ?

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

Re: How to convert rows into columns ?

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 ?
One Star

Re: How to convert rows into columns ?

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

Re: How to convert rows into columns ?

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

Re: How to convert rows into columns ?

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
One Star

Re: How to convert rows into columns ?

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

Re: How to convert rows into columns ?

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

Five Stars

Re: How to convert rows into columns ?

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

Re: How to convert rows into columns ?

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

Re: How to convert rows into columns ?

In fact, you are right on your before last reply. But me I have a tMap. And I catch the error in the tMap. I want to get the following result as you can get here :
| tLogRow_1 |
|=-+------------+------------+-----------=|
|Id|Picture_1 |Picture_2 |Picture_3 |
|=-+------------+------------+-----------=|
|1 | 7355448.jpg| 7893544.jpg| 2364758.jpg|
|2 | 1234567.jpg| 7864125.jpg| 1478523.jpg|
Can you try with a tMap ? To see if you catch the error (or not ?)
Regards
Five Stars

Re: How to convert rows into columns ?

Hi SPTIMAN,
I have created empty record on second row and then process with provided tMap solution it was fine except empty value for picture2 column.
One Star

Re: How to convert rows into columns ?

Hi,
In fact, I must obtain the following result :
| tLogRow_1 |
|=-+------------+------------+-----------
|Id|Picture_1 |Picture_2 |Picture_3 |
|=-+------------+------------+-----------
|1 | 7355448.jpg| 7893544.jpg| 2364758.jpg|
|2 | 1234567.jpg| 7864125.jpg| 1478523.jpg|
|3 | ZE554EF.jpg| | |
|4 | 5478752.jpg| 3244563.jpg| |
|5 | 1234567.jpg| 7864125.jpg| 1478523.jpg|
|6 | 6745656.jpg| 3742264.jpg| 6745456.jpg|
The error is caught at the line 3 (here) in the column 2 : empty value : that's why I catch the exception...
row1.picture.split(";") is not correct here ? That's why I have the error here
For an id, I can have only one picture or two pictures (or nothing !).
One Star

Re: How to convert rows into columns ?

Anyone ?
Five Stars

Re: How to convert rows into columns ?

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

have you used same way as i stated in my previous reply? because it will check whether array has values more than we require then it will execute or assign to the column. it is working at my place.