Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
it means ,
do you have any input which does not have two semicolons ?
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 = "";
}
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
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);