Spectrum Technology Platform

 View Only
Back to discussions
Expand all | Collapse all

Concatenate All String Values into a Comma Separated List for a Specified Field in a Group in a Spectrum Dataflow

  • 1.  Concatenate All String Values into a Comma Separated List for a Specified Field in a Group in a Spectrum Dataflow

    Employee
    Posted 10-08-2019 15:30
      |   view attached
    In SCV implementations we often update a Best of Breed customer record with the best values from all matching records representing the same customer in order to implement consolidation survivorship rules. We typically do this using the Spectrum Best of Breed stage by defining rules and actions to copy values from individual matching records to the Best of Breed Master record. However, sometimes we want to update a field in the Best of Breed record with a delimited list of all unique values from matching customer records as part of consolidation survivorship rules. One typical example of this, is the need to show IDs, such as the source system customer ids from each original record in the one Best of Breed Master record. We can do this by using the Spectrum Aggregator stage to convert flat data to hierarchical data listing all values for each group and then removing duplicate values and flattening the values into a string variable. Attached is a SCV Advanced Topic 'How To' Short Training Video on how to so this.

    ------------------------------
    Jeremy Peters: GISP, CIMP - Master Data Management & Data Quality
    Solutions Architect / Principal Consultant/ Distinguished Engineer
    Pitney Bowes Customer Information Management and Location Intelligence
    350 Jordan Rd | Troy, NY 12180 USA
    jeremy.peters@pb.com
    https://www.linkedin.com/in/jeremypeters1/
    ------------------------------


  • 2.  RE: Concatenate All String Values into a Comma Separated List for a Specified Field in a Group in a Spectrum Dataflow

    Employee
    Posted 10-09-2019 05:56
    Edited by Eric Hubert 10-09-2019 05:58
    Thank's Jeremy for publishing this tutorial. Just a trick to add: let's say in this example we want to build a unique list of keys by combining Data1, Data2 and removing Data3 keys. This can be easily done with the following groovy code:

    data['data_list_out']=(data['Data1_list']+data['Data2_list']-data['Data3_list']).toString()

    // removing null values, deduping, sorting

    data['data_list_out']=((data['Data1_list']+data['Data2_list']-data['Data3_list'])-null).unique().sort().toString()

    ------------------------------
    Eric Hubert
    PreSales Engineer
    Pitney Bowes Software France SAS
    Levallois Perret
    ------------------------------

    Original Message


  • 3.  RE: Concatenate All String Values into a Comma Separated List for a Specified Field in a Group in a Spectrum Dataflow

    Employee
    Posted 10-10-2019 07:26
    Good to see Spectrum offering ways to tackle such problems. However, it is desirable to have this as a feature in the stage. I will work with Engineering to prioritize it. Thank you for posting. 


    ------------------------------
    Himanshu Verma
    CIM - Technical Product Manager
    Noida, India
    ------------------------------

    Original Message