Open topic with navigation
When data sources contain more than one type of record in a single file, the source records may occur in a "header-detail" order, where the association of each detail record is implied by the order; that is, each detail record is a "child" of the header record that appears above it.
It is often necessary to add the header record's key to each detail record. For example, if the detail records will be loaded into a relational database, the header records' keys may be used as foreign keys.
Adding the header-record keys (along with any other header-record information) to the detail records can be done in a DMExpress task using user-defined values.
The attached example demonstrates how to add key values from header records to detail records. It takes a source file containing information about employees and their dependents, and adds each header record's key - the employee ID - to the detail records.
The source file is organized in a header-detail order as follows:
The employee and dependent record types are both comma-delimited, but contain different fields. The first field for both record types is the same and contains an integer which identifies the record type:
The use of a record-type field like this is typical for header-detail data files in actual production environments.
Each employee record contains the following fields:
Each dependent record contains the following fields:
The record type is specified in the first field for both record types. A layout named RecTypeOnly_Layout is created to define only this field. To make the task easier to develop and maintain, two named conditions are created to test this:
IsEmployeeRecord (RecTypeOnly_Layout.RecType = 1)
IsDependentRecord (RecTypeOnly_Layout.RecType = 2)
As the task is processing the source file, whenever a header record is encountered, its key - the employee ID - is recorded by the named value Save_EmployeeID:
For each detail record, the header-record key is recalled using the named value Recall_EmployeeID:
A named value, EmployeeID (not to be confused with the user defined value 'EmployeeID'), is created to drive the processing of the keys for the header and detail records. It uses the IfThenElse function to call either Save_EmployeeID or Recall_EmployeeID, depending on the record type, as follows:
IfThenElse(IsEmployeeRecord, Save_EmployeeID, Recall_EmployeeID)
Both types of records are sent to a single target file using conditional reformatting based on the named conditions:
65_Header_Details.zip, compatible with DMExpress version 7.4.3 or higher
In this example, the header and detail records are both written to the same target file. To split the output of the header and detail records to two target files, an additional task is needed due to the scoping limits of user-defined values. The two target files would require different reformats, and each reformat in a DMExpress task defines an independent scope (also called a context) for user-defined values, so each would have its own copy of the user-defined value EmployeeID.
As such, the named value Save_EmployeeID would be evaluated within the scope of the Employee target file reformat and write to its copy of EmployeeID, while Recall_EmployeeID would be evaluated within the scope of the Dependent target file reformat, and would read from its copy of EmployeeID, which would never have been set.
Therefore, to write the header and detail records to separate files, you would need to add another task to read the output from the task shown in this example and create two targets with conditional filters based on the value of the RecType field.
For more information on the context of user-defined values, see User defined values and functions in the DMExpress Help.
Typically you only add the key fields of the header records to the detail records, but you can also add any other fields. For each additional header field to be added to the detail records, an additional user-defined value would be needed.
To make the task self-documenting and easy to maintain, create a named value for each additional field to record and recall its value, similar to the EmployeeID named value in this example.
Occasionally you may encounter a data source containing header-detail records that do not contain a field which explicitly identifies the type of each record. In this case, you would need to devise a test that differentiates between the record types.
Typically, a condition can be created on some difference in the format of the header and detail records, such as a date field in one and not the other. If the record formats are the same, you would need to examine the data itself to find some difference in the values that can be tested using functions available in DMExpress.
Copyright © 2016 Syncsort All rights reserved.