Convert Column's Value to Row

[Anonymous]

Hi there,

I am looking for a way to convert column's value to row value. 

This is an input sample.

(In the actual case, I have these data inside a database)

input

And this is the expected output.

I wanted to move the value from repeating column(w/ difference seq no.) to the corresponding column. 

expected out

I heard about tUnpivotRow component, but I'm using Talend Cloud Studio (v 7.3), but I couldn't find pivot component.

Can anyone show me the JobDesign(screenshot) for achieving the expected output?

Here is the two method I tried.

1) Using tUnpivot

Job Design and Result

unpivot -- job design and result             vs          expected out          

Here, there are a few problems...

1) the column header (id, key1, value1) becomes a part of a row value....

2) the repeating column header (key2, value2, key3 ....) is still there

3) 

1.1 DBInput Schema

unpivot -- dbInput's schema

1.2 tUnpivotRow's Schema

unpivot -- tUnpivotRow's Schema

1.3 tMap

unpivot -- tMap

2) tNormalize

Job Design and Result

normalize -- job design and result

Here, there are few problems

1) 'key' and 'value' is not separated into a different column

2) manual mapping is needed == inflexible

3) id's auto increment is not working....

1.1 DBInput's Schema

normalize-- dbInput's schema

1.2 tMap

normalize -- tMap

1.3 tNormalize's Schema

normalize -- tNormalize's Schema

Thank you

[manodwhb]

Use tunpivot component which you need to download from Talend exchange.

[Anonymous]

@manodwhb 

I downloaded tUnpivot component from Talend exchange and tried to install the component, but nothing shows up in the palette.

*I am using Talend Cloud Studio v.7.3, but in the component detail page, it stated the compatibility to be up to v6.2...

Was this a compatibility problem??

I did restart once, but still the component doesn't show up.

Preference>Talend>Components

[manodwhb]

I have used in 7.1.1 ,which is working.

[Anonymous]

is there a way to achieve this without tUnpivot component ??

[manodwhb]

You can try to create a file using tmap and then read it and pupulate.
In your tmap you should create below way.

row1.col+","+row1.col2+"/n"+row.col+","+row1.col3

[Anonymous]

@manodwhb 

So you mean using tMap?? Do I need tSplitRow or any other components?

Can you show me the screenshot of the job design?

I have tried a similar way before but it didn't work.

Can you help me improve this?

For you reference this is my input.

input

and this is my expected output.

expected out

Thanks

[Anonymous]

@manodwhb 

I saw tUnpivotRow in the palette so I tried using it here.

Here is the two method I tried.  But neither works well...

1) Using tUnpivot

Job Design and Result

unpivot -- job design and result             vs          expected out          

Here, there are a few problems...

1) the column header (id, key1, value1) becomes a part of a row value....

2) the repeating column header (key2, value2, key3 ....) is still there

3) 

1.1 DBInput Schema

unpivot -- dbInput's schema

1.2 tUnpivotRow's Schema

unpivot -- tUnpivotRow's Schema

1.3 tMap

unpivot -- tMap

2) tNormalize

Job Design and Result

normalize -- job design and result

Here, there are few problems

1) 'key' and 'value' is not separated into a different column

2) manual mapping is needed == inflexible

3) id's auto increment is not working....

1.1 DBInput's Schema

normalize-- dbInput's schema

1.2 tMap

normalize -- tMap

1.3 tNormalize's Schema

normalize -- tNormalize's Schema

[manodwhb]

In tunpivot you need to specify the ID column as row key section.

[Anonymous]

@manodwhb 

Here I specified the ID column as keytUnpivot's Component

Here's the run result,,, 

tUnpivotRow's Actual Result

however the column name only becomes a row value

I wanted to move the value 'k2'~'k7' and 'v2'~'v7' under the column 'key1' and 'value1' respectively

and remove the other columns (eg. value2, key2 ...)

Expected Result

Expected Output

I feel that additional mapping might be needed but I'm not sure how I could do that.

For you reference, I modified the row value a bit here to avoid confusion

Input

Input