Complex Flat File and multple, conditional record types.
Posted: Mon Feb 14, 2011 10:06 am
Currently pondering the most elegant solution to this :-
I have an incoming mainframe file, EBCDIC, fixed 80-char width. It has a variety of different record types within the file, identified by a record type column.
First thoughts it's a no-brainer to use a Complex Flat File. BUT once we have stripped out various headers and trailers we are interested in two record types , Transaction and Addendum - Record Type 5 and 6. The transaction record has an addendum indicator and if this is set to 1 then it will be immediately followed in the file by an addendum record. Where this happens a single output record needs to be built containing information merged from both the transaction and the addendum. Unfortunately there are no key or common fields on both records, the only thing that connects transaction and addendum is the fact that the addendum immediately follows its parent transaction. Not all transactions have addendums (addenda?).
SO I am now thinking of :
Reading the file in a CFF
Using two output links
1. Transactions with no addendum. (Constraint: Rec type = 5 AND Addendum Indicator = '0') - use CFF to parse into columns.
2. Transactions with addendums, and addendums. (Constraint: (Rec type = 5, AND Addendum Indicator = '1') OR Rec type = 6)
Write the output from link 2 to a transformer, for each Transaction record write the record to a Stage Variable, without writing it forward, when the Addendum row arrives write a record containing the required columns from the Transaction and Addendum then downstream combine these into the required format using some combination of Transformer and Column Import (I have some zoned decimals to parse out). Add in a few checks (e.g. every expected addendum actually exists), and funnel with Link 1 (Once we've done this, sort order becomes irrelevant).
I am not a Complex Flat File Stage guru by any means, so I was wondering if there is a more elegant solution using the native capabilities of this stage? I've posted this in the PX forum, however there is no objection to doing this in Server it it is easier ....
I have an incoming mainframe file, EBCDIC, fixed 80-char width. It has a variety of different record types within the file, identified by a record type column.
First thoughts it's a no-brainer to use a Complex Flat File. BUT once we have stripped out various headers and trailers we are interested in two record types , Transaction and Addendum - Record Type 5 and 6. The transaction record has an addendum indicator and if this is set to 1 then it will be immediately followed in the file by an addendum record. Where this happens a single output record needs to be built containing information merged from both the transaction and the addendum. Unfortunately there are no key or common fields on both records, the only thing that connects transaction and addendum is the fact that the addendum immediately follows its parent transaction. Not all transactions have addendums (addenda?).
SO I am now thinking of :
Reading the file in a CFF
Using two output links
1. Transactions with no addendum. (Constraint: Rec type = 5 AND Addendum Indicator = '0') - use CFF to parse into columns.
2. Transactions with addendums, and addendums. (Constraint: (Rec type = 5, AND Addendum Indicator = '1') OR Rec type = 6)
Write the output from link 2 to a transformer, for each Transaction record write the record to a Stage Variable, without writing it forward, when the Addendum row arrives write a record containing the required columns from the Transaction and Addendum then downstream combine these into the required format using some combination of Transformer and Column Import (I have some zoned decimals to parse out). Add in a few checks (e.g. every expected addendum actually exists), and funnel with Link 1 (Once we've done this, sort order becomes irrelevant).
I am not a Complex Flat File Stage guru by any means, so I was wondering if there is a more elegant solution using the native capabilities of this stage? I've posted this in the PX forum, however there is no objection to doing this in Server it it is easier ....