Multiple Delimiters and Pivot
Posted: Wed Apr 09, 2008 11:21 pm
Hi!!
I have a source feed in the following pattern:
INFILE
============
COL1,COL2,COL3,COL4,COL5
987,2,3~7~5,400~468~598,0005~4687~5980
1111,2,2~7,400~468,0005~897
The file is mainly delimited by comma(,).
Further in COL3,4&5 it has subdelimiter as tilde(~)
Further within COL3,4 &5 no of ~ may vary.But no of ~ in COL3 is same as that in COL4 & COL5.
I need output in the following pattern
COL1,COL2,COL3,COL4,COL5
987,2,3,400,0005
987,2,7,468,4687
987,2,5,598,5980
1111,2,2,400,0005
1111,2,7,468,897
Here,output is generated by following logic:
In the input file,COL1,COL2 are preserved.
Then First value before ~ is taken from COL3,COL4& COL5 resp and written in output.
I have tried this routine to achieve,but within loop I am only getting the final value & no all values.
A=Arg1
A1=Field(A,',',1); *COL1
A2=Field(A,',',2); *COL2
A3=Field(A,',',3); *COL3 with TILDE
A4=Field(A,',',4); *COL4 with TILDE
A5=Field(A,',',5); *COL5 with TILDE
B=DCount(A3,'~'); *Count i.e. no of times the for loop will execute
For Outer = 1 To B Step +1 ;* outer B no of repetitions
C1=Field(A3,'~',Outer);
D1=Field(A4,'~',Outer);
E1=Field(A5,'~',Outer);
Final=A1:'|':A2:'|':C1:'|': D1:'|':E1;
OPENSEQ '/u04/PRODDSS/data2/srcfiles/test/ramya/server/inrich/file1' TO SeqFile ELSE ABORT
WRITESEQ Final TO SeqFile THEN PRINT Final ELSE STOP
Next Outer
CLOSESEQ SeqFile
Ans =Final
Any Help is appreciated
I have a source feed in the following pattern:
INFILE
============
COL1,COL2,COL3,COL4,COL5
987,2,3~7~5,400~468~598,0005~4687~5980
1111,2,2~7,400~468,0005~897
The file is mainly delimited by comma(,).
Further in COL3,4&5 it has subdelimiter as tilde(~)
Further within COL3,4 &5 no of ~ may vary.But no of ~ in COL3 is same as that in COL4 & COL5.
I need output in the following pattern
COL1,COL2,COL3,COL4,COL5
987,2,3,400,0005
987,2,7,468,4687
987,2,5,598,5980
1111,2,2,400,0005
1111,2,7,468,897
Here,output is generated by following logic:
In the input file,COL1,COL2 are preserved.
Then First value before ~ is taken from COL3,COL4& COL5 resp and written in output.
I have tried this routine to achieve,but within loop I am only getting the final value & no all values.
A=Arg1
A1=Field(A,',',1); *COL1
A2=Field(A,',',2); *COL2
A3=Field(A,',',3); *COL3 with TILDE
A4=Field(A,',',4); *COL4 with TILDE
A5=Field(A,',',5); *COL5 with TILDE
B=DCount(A3,'~'); *Count i.e. no of times the for loop will execute
For Outer = 1 To B Step +1 ;* outer B no of repetitions
C1=Field(A3,'~',Outer);
D1=Field(A4,'~',Outer);
E1=Field(A5,'~',Outer);
Final=A1:'|':A2:'|':C1:'|': D1:'|':E1;
OPENSEQ '/u04/PRODDSS/data2/srcfiles/test/ramya/server/inrich/file1' TO SeqFile ELSE ABORT
WRITESEQ Final TO SeqFile THEN PRINT Final ELSE STOP
Next Outer
CLOSESEQ SeqFile
Ans =Final
Any Help is appreciated