Open topic with navigation
XSUM is a mainframe/JCL sort deduplication function that writes each first record with a unique key value to one target, and writes any subsequent duplicate-keyed records to another target.
This functionality can be achieved in DMExpress using cross-record logic via conditions and user defined values in a sort task.
The attached example demonstrates how to implement the XSUM function in DMExpress.
The source file in this example contains records with a phone number for a given person as of a given date, sorted in date descending order, with the layout defined as follows:
We want to write the record containing the most recent PhoneNumber associated with a given Name to one target, and any records containing an old PhoneNumber associated with the same Name to another target. At the end of the task, the first target should contain exactly one record per person with their most recent phone number, and the second target should contain all the duplicate records.
By clearing the Original order of equal-keyed records need not be maintained checkbox, we make the sort stable. Since the records were originally sorted in date descending order, we're guaranteed that the record with the most recent phone number for any given person will be first in a set of equal-keyed records.
As the target logic reads through the sorted records, the value SetCurrentKey keeps track of the key (Name) it's currently on and stores it in a user defined value (UDV) named currentkey. When the first record is read, currentkey is unset.
The condition IsDuplicate calls the condition IsEqualToCurrentKey, which compares the Name of the current record to the value of currentkey.
The output logic is driven by the filter on each of the two targets. As each target reads the sorted records, it applies the associated target filter, which evaluates the condition IsDuplicate.
Note that the scope of UDVs is per target, so each target has its own copy of currentkey as it evaluates the filter condition.
178_XSUM.zip, compatible with DMExpress version 8.0.5 or higher
For more information on user defined values, see User defined values and functions in the DMExpress Help.
Copyright © 2016 Syncsort All rights reserved.