Hierarchical Data Files

Estimated time: 30 Mins

Hierarchical files are those which have header/detail/sub-detail record types within a file.

Method

  1. Import Hierarchical CSV File
  2. Set Hierarchical Dataset Options
  3. Organise the Hierarchy

The below screenshot illustrates a hierarchical file, where the first column denotes the record ‘type’ .

Import Hierarchical CSV File

  1. Create a new integration, drag a CSV Read transform onto the integration, double click to open it.
  2. Set the various options in File Options:
  1. File Path
    • C:\IMan\InputData\Training
  2. File Name
    • HierarchicalDataInput.csv
  3. Field Delimiter
    • ,
  4. Header Records
    • 6
  5. Mapping Style
    • By Position

Set Hierarchical Dataset Options

  1. Expand the Hierarchical Dataset Options:
  1. Hierarchical Dataset
    • Select an option from the Hierarchical Dataset drop-down to enable hierarchical data read.
    • For training, select: Keyed Fields

  1. Press Refresh, and the table will be populated with the values it could find to denote the record type.

When setting up a hierarchical dataset, you must have a file which includes all expected record types.

  1. If you have a file which does not have a record type, a prompt to delete the type will be displayed:

Organise the Hierarchy

  1. Click the Field Mapping tab.
  1. Rename Transaction Id:
    • Header

  1. Press the edit button to configure the Header fields:
  1. Field Name
    • For training: RECTYPE
  2. Import
    • Select to import the field
    • For training: all selected.
  3. Type
    • Select appropriate field type.
    • For training: Integer
  4. Key
    • To define the relationships between records.
    • For training: Leave as 0
  1. Repeat for all header fields, using the appropriate settings.

Ensure the Key value for SEQ field is set to 1- this is the field with which the data is associated.

  1. When done, select '2' from the transaction identifier drop down.

  1. Rename the Transaction Id
    • Detail (or Material).
  1. Change the Parent Id to Header.
  2. Enter the detail fields in the same way as for the header fields.
  1. Set key values:
    • SEQ
    • 1
    • LINENO
    • 2

This Key value denotes the field that defines the hierarchical relationship. See Key Fields for more information.

  1. Select '5' from the transaction identifier drop down.
  1. Rename the Transaction Id
    • SerialNo
  1. Change the Parent Id to Detail
  2. Enter the SerialNo fields, in the same way as for the detail and header fields.
  1. Set the key values:
    • SEQ
    • 1
    • LINENO
    • 2
    • SERIALNUMF
    • 3

  1. Press Refresh; the preview Grid should look something like below: