Multiple Delimiters and Pivot

[loe_ram13]

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

[ray.wurlod]

That's because you are re-opening the file (and thus positioning to beginning of file) within the loop.

Open the file before you start the loop and don't forget to close the file (which releases the lock that OpenSeq sets) after the loop and before returning.

You could use a Pivot stage (with some upstream re-parsing of the data) if you did not wish to maintain code.

[loe_ram13]

That's because you are re-opening the file (and thus positioning to beginning of file) within the loop.

Open the file before you start the loop and don't forget to close the file (which releases th ...

I do not have rights to view the Premium content.
It would be really kind if you post the same as normal post as I am urgently in need of the solution.ThankYou.

[Rubu]

Even though you are not the premium member you got all the information needed here.

Ray: Open the file before you start the loop and don't forget to close the file (which releases the lock that OpenSeq sets) after the loop and before returning

But, taking a premium membersip will be a better Idea as you may not be so lucky every time

[loe_ram13]

I tried using the OPENSEQ before the loop and also closed before returning with the following routine.

A=Arg1
A1=Field(A,',',1); *MDN
A2=Field(A,',',2); *MIN
A3=Field(A,',',3); *BUCKET ID with TILDE
A4=Field(A,',',4); *BUCKET VAL with TILDE
A5=Field(A,',',5); *COSID with TILDE
B=DCount(A3,'~'); *Count i.e. no of times the for loop will execute
OPENSEQ '/u04/PRODDSS/data2/srcfiles/test/ramya/server/inrich/file1' TO SeqFile ELSE ABORT
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;
WRITESEQ Final TO SeqFile ELSE STOP
Next Outer
CLOSESEQ SeqFile
Ans =Final


But I am getting the following error while trying to test after compilation:

Error Calling SubRoutine:DSR EDIT (Action=2);Check Datastage is set up correctly.