Uploaded image for project: 'HPCC'
  1. HPCC
  2. HPCC-16290

New BEST option for DEDUP

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 6.2.0
    • Fix Version/s: 7.0.0
    • Component/s: Documentation
    • Labels:

      Description

      BEST(<sort-list>) – new option for DEDUP

      DEDUP(recordset [, condition [, ALL[, HASH]] [, BEST(<sort-list>)] [, KEEP n ] [, keeper ] ] [, LOCAL]) 

      The BEST option provides additional control over which records are retained from a set of “duplicate” records. The first in the <sort-list> order of records are retained. The sort-list is comma delimited list of fields. The fields may be prefixed with a minus sign to require a reverse sort on that field.

      DEDUP(recordset, field1, BEST(field2) ) means that from set of duplicate records, the first record from the set duplicates sorted by field2 is retained. DEDUP(recordset, field1, BEST(-field2) ) produces the last record sorted by field2 from the set of duplicates.

      BEST option cannot currently be used with a KEEP parameter greater than ‘1’.

      Example code:

      SomeFile := DATASET([{'001','KC','G'}, 
                           {'002','KC','Z'}, 
                           {'003','KC','Z'}, 
                           {'004','KC','C'}, 
                           {'005','KA','X'}, 
                           {'006','KB','A'}, 
                           {'007','KB','G'}, 
                           {'008','KA','B'}],{STRING3 Id, String2 Value1, String1 Value2});
      
      SomeFile1 := SORT(SomeFile, Value1);
      
      DEDUP(SomeFile1, Value1, BEST(Value2));
      // Output: 
      // id value1 value2
      // 008 KA B
      // 006 KB A
      // 004 KC C
      
      DEDUP(SomeFile1, Value1, BEST(-Value2));
      // Output:
      // id value1 value2
      // 005 KA X
      // 007 KB G
      // 002 KC Z
      
      DEDUP(SomeFile1, Value1, HASH, BEST(Value2));
      // Output: 
      // id value1 value2
      // 008 KA B
      // 006 KB A
      // 004 KC C
      
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                jamesdefabia Jim DeFabia
                Reporter:
                shamser Shamser Ahmed
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: