topic Re: Convert Column's Value to Row in Talend Studio

Convert Column's Value to Row

Anonymous — Sat, 16 Nov 2024 02:56:32 GMT

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

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

Re: Convert Column's Value to Row

manodwhb — Thu, 19 Mar 2020 05:54:53 GMT

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

Re: Convert Column's Value to Row

Anonymous — Thu, 19 Mar 2020 08:17:01 GMT

@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

Re: Convert Column's Value to Row

manodwhb — Thu, 19 Mar 2020 16:58:31 GMT

I have used in 7.1.1 ,which is working.

Re: Convert Column's Value to Row

Anonymous — Mon, 23 Mar 2020 02:20:25 GMT

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

Re: Convert Column's Value to Row

manodwhb — Mon, 23 Mar 2020 03:05:53 GMT

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

Re: Convert Column's Value to Row

Anonymous — Mon, 23 Mar 2020 04:03:33 GMT

@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

Re: Convert Column's Value to Row

Anonymous — Mon, 23 Mar 2020 10:50:21 GMT

@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

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

Re: Convert Column's Value to Row

manodwhb — Mon, 23 Mar 2020 11:51:08 GMT

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

Re: Convert Column's Value to Row

Anonymous — Mon, 23 Mar 2020 12:54:35 GMT

@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

Re: Convert Column's Value to Row

manodwhb — Tue, 24 Mar 2020 04:17:01 GMT

You can mark as slove this case also so that it will help to others.

Re: Convert Column's Value to Row

Anonymous — Tue, 24 Mar 2020 04:58:01 GMT

solved here
https://community.talend.com/t5/Design-and-Development/Column-to-Row-Transformation-Output-data-and-log-differs/m-p/216486#M119074