topic Transpose in Talend Studio

Transpose

sszzxx — Thu, 21 Mar 2019 07:57:32 GMT

Hi all,

I was just wondering if it is possible to transpose some data columns ?

src:
CNUM; ANUM; TXN1; TXN2; TXN3
A;B;101;102;103
C;D;104;NULL;106

required :

CNUM;ANUM;TXN
A;B;101
A;B;102
A;B;102
C;D;104
C;D;NULL
C;D;106

I want to eliminate the TXN1, TXN2 AND TXN3 and consolidate them all into TXN with the respective CNUM and ANUM.

Thanks in advance.

Re: Transpose

akumar2301 — Thu, 21 Mar 2019 11:05:53 GMT

1) Read your input using tFileInputFullLine

2) using tJavaRow , split it two part

Expression :

int file2ndsemicolon = input_row.line.indexOf(";", input_row.line.indexOf(";") + 1);
output_row.Key = input_row.line.substring(0, file2ndsemicolon);
output_row.rest = input_row.line.substring(file2ndsemicolon + 1);

input

A;B;101;102;103
C;D;104;NULL;106

output

A;B|101;102;103

C;D|104;NULL;106

3) tNormalise of 2nd Column and separator ";".

4) tFileOutputDelimited with delimiter ";".

Final output

A;B;101
A;B;102
A;B;103
C;D;104
C;D;NULL
C;D;106

Re: Transpose

sszzxx — Thu, 21 Mar 2019 15:43:43 GMT

Thanks for the help Abhishek!

I am facing an issue now in the tJavaRow..

tFileInputFullRow --> tJavaRow --> tNormalize --> tLogRow

it is showing me this error:

tJavaRow_1 String index out of range: -1
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(String.java:1967)

May I know how to overcome this? As this seems to disable me from running another subjob on cue with the Trigger on Subjob Ok trigger.

Thanks for the help in advance

Re: Transpose

akumar2301 — Thu, 21 Mar 2019 15:45:32 GMT

it means ,

do you have any input which does not have two semicolons ?

Re: Transpose

akumar2301 — Thu, 21 Mar 2019 15:51:53 GMT

or put this in try Catch

try {

}catch(Exception e){

output_row.Key = input_row.line;

output_row.rest = "";

}

Re: Transpose

sszzxx — Sun, 24 Mar 2019 13:06:34 GMT

Hi.

It was working as expected. Thanks!

How do I go about if there is another 3 new columns added and for that also I have to transpose them?

Source:

ACCT;NAME;COLA1;COLA1;COLA3;COLB1;COLB2;COLB3

A1;N1;AA;AB;AC;BA;BB;BA

Expected result:

ACCT;NAME;COLA;COLB;

A1;N1;AA;BA

A1;N1;AA;BB

A1;N1;AA;BC

A1;N1;AB;BA

A1;N1;AB;BB

A1;N1;AB;BC

A1;N1;AC;BA

A1;N1;AC;BB

A1;N1;AC;BC

Re: Transpose

akumar2301 — Sun, 24 Mar 2019 18:11:29 GMT

int file3rdsemicolon = input_row.line.indexOf(";",input_row.line.indexOf(";", input_row.line.indexOf(";") + 1)+1);

output_row.Key = input_row.line.substring(0, file3rdsemicolon);
output_row.rest = input_row.line.substring(file3rdsemicolon + 1);