Writing Flat Data to a Variable Format File

In a Spectrum™ Technology Platform dataflow each record has the same fields. However, in a variable format file, not all records contain the same fields. In order to write flat data from a dataflow to a variable format file you need to break up each record in the dataflow, grouping the fields from each record into list fields corresponding to the record types you want to use for the variable format file. A list field is a collection of fields. For example, the fields FirstName, LastName, Gender, Address, and Phone could be grouped together into a list field called AccountOwner.

To write flat data to a variable format file, use an Aggregator stage to group fields into list fields corresponding to the record types you want to write to the variable format file. To do this:

  1. Place an Aggregator stage in your dataflow anywhere upstream from the Write to Variable Format File stage.
  2. Double-click the Aggregator stage to open its options window.
  3. Select Group by then click Add.
  4. In the Group By field, select the field that contains a unique identifier that can be used to identify related data. This field's value should be unique across the records in the flat data. For example, an account number, a social security number, or a phone number.
    Note: The field you select should be sorted. If it is not, use a Sorter stage to sort the records by the field.
  5. Click OK.
  6. Select Output lists then click Add.

    Each output list will represent one record type in the variable format file.

  7. Select New data type and in the Type name field specify the type of information that will be contained in this data type. This will become a record type in the variable format file. For example, this data type will contain records related to account transactions, you could name the type "AccountTransaction".
  8. In the Name field, enter the name you want to give to this field. This may be the same name you specify in the Type name field.
  9. Click OK.
  10. Select the data type you just created and click Add.
  11. Leave the option Existing field selected and select one of the fields you want to include in this data type then click OK. Remember that this will become a record type in the variable format file. Repeat to add additional fields to this record type.
  12. Create additional output lists for each record type you want to have in the variable format file. When finished, click OK to close the Aggregator options.

The fields coming out of the Aggregator stage are now grouped into list fields that correspond to the record types you want to include in the variable format file output.

For example, given this flat data:

FIRSTNAME,LASTNAME,ADDRESS,ACCOUNTNUMBER,DATE_OPENED,TRANSACTION_NUMBER,TRANSACTION_DATE,AMOUNT
Joe,Smith,100 Main St,CHK12904567,12/2/2007,1000567,1/5/2012,323.12

You would want to convert it to something like this in the variable format file:

AccountOwner        Joe,Smith,100 Main St
AccountInformation  CHK12904567,12/2/2007
Transaction         1000567,1/5/2012,323.12

To accomplish this, you would create an Aggregator stage that is configured like this: