Flattening Variable Format Data

Variable format file data often contains records that have a hierarchical relationship, with one record type being a parent to other record types. Since many stages require data to be in a flat format, so you may have to flatten the data in order to make the data usable by downstream stages. For example, consider this input data:

001   Joe,Smith,M,100 Main St,555-234-1290
100   CHK12904567,12/2/2007,6/1/2012,CHK
200   1000567,1/5/2012,Fashion Shoes,323.12
001   Anne,Johnson,F,1202 Lake St,555-222-4932
100   CHK238193875,1/21/2001,4/12/2012,CHK
200   1000232,3/5/2012,Blue Goose Grocery,132.11
200   1000232,3/8/2012,Trailway Bikes,540.00

You may want to flatten the records so that you have one record per transaction. In the above example, that would mean taking the transaction records (records with the tag 200) and flattening them to include the account owner information (records with the tag 001) and the account details (records with the tag 100).

The following procedure describes how to use Splitter stages to flatten records.

  1. Add a Read from Variable Format File stage to your data flow and configure the stage. For more information, see Read from Variable Format File.
  2. Add a Splitter stage and connect it to Read from Variable Format File.
  3. Add additional Splitter stages as needed so that you have one splitter stage for each child record type in your input data.
  4. Connect all the Splitter stages.

    You should now have a data flow that looks like this:

  5. Double-click the first Splitter stage to open the stage options.
  6. In the Split at field, select one of the child record types.
  7. Click OK.
  8. Configure each additional Splitter stage, selecting a different child record type in each Splitter's Split at field.