Transpose

[sszzxx]

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.

[akumar2301]

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

[sszzxx]

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

[akumar2301]

it means , 

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

[akumar2301]

or put this in try Catch

try {

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);

}catch(Exception e){

output_row.Key = input_row.line;

output_row.rest = "";

}

[sszzxx]

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

[akumar2301]

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);