topic Re: [resolved] Unpivot / Talend component question in Talend Studio

[resolved] Unpivot / Talend component question

Anonymous — Sat, 16 Nov 2024 14:03:09 GMT

Hi,
I'm wanting to convert:
userid, usersignature, prop1, prop2, prop3,prop*
blah,20090303170000,val1, val2, val3, val*
into 2 files:
userid, usersignature
blah, 20090303170000
userid, propname, propvalue
blah, prop1, val1
blah, prop2, val2
blah, prop3, val3
...
I've searched these forms and found that there's a component that has been created to do this:
18764
I've only just worked out (as I'm writing this!) that java projects need java components, and perl projects need perl components - which is why that component doesn't appear within my Java project palette. I don't really understand how these components work, so I don't know how easy it would be to make the above component available in Java. Could someone please comment on this?
Alternatively, does anyone have any advice on how to get the result that I'm looking for above? The input file will be text, and I'd like the output to be a bulk insert into an Oracle DB.
Any help would be appreciated

Re: [resolved] Unpivot / Talend component question

Anonymous — Wed, 04 Mar 2009 02:38:40 GMT

Hello

userid, usersignature
blah, 20090303170000

It is very easy to get the first output file, I think you have got it.

userid, propname, propvalue
blah, prop1, val1
blah, prop2, val2
blah, prop3, val3
...

See the related 5668, please try to reproduce the job.
Feel free to ask for help on forum if you meet any questions!
Best regards

shong

Re: [resolved] Unpivot / Talend component question

Anonymous — Fri, 06 Mar 2009 19:49:04 GMT

Thanks for the suggestion, however I'm having trouble getting it working.
For an input of:
col1, col2, col3, col4, col5
val11, val12, val13, val14, val15
val21, val22, val23, val24, val25
I'm getting an output of:
col1|val15
col2|val15
col3|val15
col4|val15
col5|val15
i.e. it's giving me the column names correctly, but it always returns the column value as whatever the last value of the 2nd line is.
Any idea what would cause that? I've compared the settings against the example over and over.
AND, it only ever returns the first line of values - nothing from the second line of values is ever output.
Cheers,

Re: [resolved] Unpivot / Talend component question

Anonymous — Tue, 17 Mar 2009 11:33:25 GMT

Thanks for the update on the other thread Shong, however that now only gives me the output of the first row of data - I now need to work out how to get it to return all rows in my file.
I've tried changing the tSampleRow_2 to return a greater sample range, but it still only returns the first row of data. I tried removing the sampleRow component altogether with the same result. I'm assuming this is because it's tied to only bringing back 1 row from the top half of the process (the headers).
So how do I get the top half to re-run for every row of the bottom half?
Cheers

Re: [resolved] Unpivot / Talend component question

Anonymous — Fri, 17 Apr 2009 16:35:59 GMT

I gave up trying to do it with built-in components and ended up writing my own. I've submitted it to the exchange in case anyone else needs it:
148

Re: [resolved] Unpivot / Talend component question

Anonymous — Mon, 20 Apr 2009 08:26:44 GMT

Hello daztop
Thanks for your component!!!
Can you share us a use case here?
Thanks again.
Best regards

shong

Re: [resolved] Unpivot / Talend component question

Anonymous — Fri, 03 Jul 2009 11:16:08 GMT

The component seems to work very well, thank you for creating this!
However, it does not handle null values correctly.

row2.pivot_key = field;
try {
	row2.pivot_value = row1.getClass()
			.getDeclaredField(field).get(row1)
			.toString();
} catch (Exception e) {
	// Can't happen - the field was there to have
	// created the array in the first place
}

The problem with this is that if the field contains a null value, it will throw a NullPointerException. Since this is caught by the catch-clause, pivot_value will just keep the value of the previous field, which is quite confusing. I'd suggest replacing the "Can't happen"-comment with "row2.pivot_value = null;".

Re: [resolved] Unpivot / Talend component question

Anonymous — Thu, 30 Jul 2009 13:16:54 GMT

Hi daztop,
I have tested your Java component tUnpivotRow followiing the use case developped by plegall in 18764].
The scenario works with the Perl component but with the Java component I get the following error message. I have tested the scenario a few times with different files or settings but the error is always the same.
I am not a Java specialist, could someone explain where the error comes from so I can fix it?
Thanx

Re: [resolved] Unpivot / Talend component question

Anonymous — Mon, 30 Nov 2009 16:55:03 GMT

Has anyone got this component to work? From the description, this is exactly what I'm looking for, but I haven't got it to work. I've loaded the component (and surfaced it within the component pallet), but I'm still getting errors when attempting to run.
Can anyone provide a quick how-to/tutorial (with screenshots) on the java-based tUnpivotRow component?

Re: [resolved] Unpivot / Talend component question

Anonymous — Mon, 30 Nov 2009 21:09:09 GMT

Sorry for not responding to your direct email sooner bbeliaso.
The most basic example would be:
tFileInputDelimited -> tUnpivotRow -> tFileOutputDelimited
Connect the 3 components together in that order, and configure the file input and file output accordingly - ensure that you set up the schema for the file input and then cascade it through to the tUnpivotRow.
Now click the tUnpivotRow component and view the component configuration tab.
Under the Row Keys area, add all the columns that you want to remain fixed.
e.g. when the input is:
columnKey,property1,property2,property3,property4
------------------------------------------------------------
myKey,value1,value2,value3,value4
And you want:
columnkey,keyName,keyValue
-----------------------------------
myKey,property1,value1
myKey,property2,value2
myKey,property3,value3
myKey,property4,value4
Then your row key will just be columnkey - that's the only that you need to add. All other columns then become rows in your output file. You can add as many columns as you like to this, and they'll all be passed through for every line of output.
The output schema for the tUnpivotRow component will then be equal to all of your row keys, plus the fixed pivot_key and pivot_value columns.
Does that help you out?

Re: [resolved] Unpivot / Talend component question

Anonymous — Mon, 30 Nov 2009 22:07:05 GMT

Thanks for the reply Daztop. Yes, I got this component working. The part that threw me off was that I had to ad the Key column to the tUnpivotRow schema in order to make it work (even after defining it as an input for "Row keys"). See screen shot to see what my final solution was. Once I got past this it was fine.
Thanks,

Re: [resolved] Unpivot / Talend component question

Anonymous — Mon, 30 Nov 2009 23:53:37 GMT

The part that threw me off was that I had to ad the Key column to the tUnpivotRow schema in order to make it work (even after defining it as an input for "Row keys").

At the time of writing the component, there was no way to programatically change the output schema (I don't know if this has now changed). It's therefore unfortunately reliant on the user to set it up in both places.
Glad you got it working.

Re: [resolved] Unpivot / Talend component question

Anonymous — Wed, 15 Dec 2010 15:50:46 GMT

Daztop...hats off to you . Thanks a lot for this component. This helped to resolve my problem .

Re: [resolved] Unpivot / Talend component question

_AnonymousUser — Mon, 21 Mar 2011 19:58:03 GMT

The component does not work with Talend 4.0.2. Can it be fixed?

Re: [resolved] Unpivot / Talend component question

Anonymous — Tue, 22 Dec 2015 15:50:56 GMT

Hi, I am able to download this tunpivot component,but it's not showing in pallete,so not able to use the component. Please Help me.

Re: [resolved] Unpivot / Talend component question

Anonymous — Wed, 23 Dec 2015 06:33:07 GMT

Hi snehasisrath
I have tried to install this component to Talend Studio 6.0.1 and it works, please refers to this article about how to install a custom component to studio.

Re: [resolved] Unpivot / Talend component question

Anonymous — Fri, 08 Jun 2018 12:04:05 GMT

Hey, I'd like to see the java's code. I can't find it with the 148, can you provide me the code or the link, please?

Thank you!

Re: [resolved] Unpivot / Talend component question

Anonymous — Thu, 25 Apr 2019 04:29:21 GMT

hi all,

can any one update the unpivot component to be compatible with talend open studio 7.1