Fixedwidth file logic

[dodda]

Hello

I have a requirement of reading a fixed width file. The sample data looks like below.

FR ABC 3302773001 9 00125993103000000020000000
FR ABC 3302774001IJKL5678
FR ABC 3302773002 9 00095867102000000160000000
FR ABC 3302774002IJKL5123
FR ABC 3302773003 9 00024017102000000060000000
FR ABC 3302774003IJKL4567
FR ABC 3302773004 9 00878802102000000580000000
FR ABC 3302774004TM001234
FR ABC 3302773005 9 00864953102000000120000000
FR ABC 3302774005IJKL3456
FR ABC 3302773006 9 00787226102000000080000000
FR ABC 3302774006TM007869
FR ABC 3302773007 9 00786397103000000060000000
FR ABC 3302774007IJKL4343
FR ABC 3302773008 9 00630289102000000040000000
FR ABC 3302774008TM004654


My sample data description of first record (Block 3) with various fields are FR (country code) ABC (sender code) 330277 (AL number) 3 ( BlockId ) 001 (AL Lineid) 9 (type of AL) 001259(CAI)

My sample data description of second record (Block 4) with various fields are FR (country code) ABC (sender code) 330277 (AL number) 4 ( BlockId ) 001 (AL Lineid) IJKL (Instrct code) 5678 (CCID code)


I need to produce an output file with CAI of BLOCK3 records + CCID where Instrct code is TM00 of the corresponding BLOCK4 record associated with the BLOCK3 record I need to Use the AL Number and AL Line ID to find the corresonsing block4 record for the block3 record)

Ex: In the out put file I need as below

0087881234
0078727869
0063024654


Can anybody please help me in getting through this logic.I appreciate your help.

Thanks

Vimal

[ShaneMuir]

Read the file in with a sequential file stage and send to a copy stage (or even a transform) . Split the stream into block 3 and block 4 records using the key as country code, sender code, al number and al line id; and filter out the block 4 records which are TM00. Then using a join stage join the records back together (using above key) with the relevant fields. You can then build your output with the relevant fields.

[Rubu]

Another way of achieving it may be...

SQLSTG---->TX---->Target STG

1)Read all the rows as variable string
2)Keep the previous detail(of block 3) in stage variables
3)Append Necessary detail from Block 4 to the previous detail.
4) Put constraint as Block=4 in TX.

This should work only for Sequential processing of TX stage.

Otherwise split the rows before feedind the transformer in necessary columns in a modify stage and Partition on AL Number and AL Line.

[bkumar103]

I dont think the records can be splitted using the copy stage. It would be better if you use filter stage or transformer stage. The job may be as follow:

sequential file ------> filter stge --> block 3 records ------------------ |
| |
|_> block 4 records -> filter the |
record for -------- Join
TM00 stage
based on AL
and AL line id

Dont forget to split the record as and when required to filter on respective field use proper stages.


Thanks,
Birendra

[ShaneMuir]

Actually thinking about it further it will be better to have a transform after reading the sequential file. Since your rows seem to be of a variable length you will have to create a few variables to extract the relevant fields. Set 2 output links with the contraints of block3 and block4 then run into the join on your key. Eg:

Code: Select all

                      ,---Block3----\
SeqFile -------  Xfm <               > Join ------ Output
                      `---Block4----/