Open topic with navigation
In order to retain only records with duplicate keys, you can use a DMExpress Aggregate task to count the number of records with a given key, and then use a filter to output only those records whose key count is greater than 1.
The attached example demonstrates how to retain only the records that contain duplicate keys using a DMExpress aggregate task.
The source file in this example contains customer data at a cable company, including Account Number, Customer Name, and subscribed Service(s):
To find which customers qualify for double or triple play discounts, we want to retain one record for each customer subscribed to more than one service.
In the Aggregate dialog, we group by the key field, Account Number, and check the Count summarized records checkbox to add the Count all function to the Summarize list, which will provide the record count for each group.
We add the Customer Name to the Summarize list, specifying the Any function (the same name should always be associated with the same account number), so that the name is also available for output, but we don’t care about the Service(s); we simply want to output one record for each customer that has more than one service.
The result of the Count all summary is made available via the count() summary value, which we use to define the condition MoreThanOneService as having a count greater than 1:
The MoreThanOneService condition is used to filter the output records, thereby retaining only customers subscribed to more than one service with the cable company.
Finally, in the Reformat, we add the Account Number, Customer Name, and the service count, producing the following output:
224_Retain_only_duplicate_recs.zip, compatible with DMExpress version 8.0.5 or higher
For more information on the summarization functions available in a DMExpress Aggregate task, see Defining the summaries in the DMExpress Help.
For an example of how to perform XSUM in DMExpress, where all uniquely-keyed records and first of duplicate-keyed records are written to one target, and the remaining duplicate-keyed records are written to another target, see How to Perform XSUM Deduplication.
Copyright © 2016 Syncsort All rights reserved.