Versions Compared

Old Version 8

changes.mady.by.user Anthony George

Saved on

compared with

New Version 9

changes.mady.by.user Anthony George

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Insert excerpt
_Banners
_Banners
nameanalysis
nopaneltrue

Introduction

Sometimes it is necessary to split a single record into multiple records, for example if a list of values are provided in a single attribute

for one record

field.

Scenario

A list of values exist in an attribute, and we want to create a record for each item in the list. For example, the list may have come from a web service, and the identifiers need to be in separate records for further processing.

The scenario we will cover in this solution will be

:

a stream with one record

-

and inside one of its attributes exists a comma separated list of 10 values

and this

. This record will be processed such that it results in 10 records, each with a different one of the 10 values

Starting Recordset.

Customer
Products
Product
BobProduct 1, Product 2, Product 3... Product 10
MargretProduct 1

Target Recordset

CustomerProduct
BobProduct 1
BobProduct 2
Bob...
BobProduct 10
MargretProduct 1

Solution

Solution:

  • Create an output multiplier expression to parse the comma separated values into a list of 10 values
  • Reference the values in that list with the _type internal variable.
Insert excerpt_terms_changing_terms_changingnopaneltrue
  1. Open the properties of the Table where we will be splitting the records
  2. In the outpuMultiplier we will configure a PhixScript to split the records. An outputMultiplier needs to produce an array, and each element will be treated as a record. Enter the following:


    1. Code Block
      do( 
    // Create a place holder for the Records that will be passed out of 
	// the Output Multiplier
    $recordsOut = [],

    // For each incoming record, process each one in turn
    forEach($recordIn, in,
    
        // Split the attribute by the comma delimiter
        $productList = split($recordIn.Product, ","),
        
        // For each of the products in the attribute create a record
        forEach($product, $productList,
        
            // Create an empty array to hold the split record
            $splitRecord = [],
        
            // Add the Customer Name to the first element
            addElement($splitRecord, $recordIn.CustomerName),
            
            // Add the Product to the second element
            addElement($splitRecord, $product),
            
            //Add the Split Record to the recordset that will be output
            addElement($recordsOut, $splitRecord)
            
        )
    ),
    
    // Set the recordsOut as the output from this PhixScript
    $recordsOut
)


  3. Click 
    Insert excerpt
    _finish
    _finish
    nopaneltrue
     to save your changes.
  4. Run Analysis to see the results.