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 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. This record will be processed such that it results in 10 records, each with a different one of the 10 values

Starting Recordset.

Target Recordset

Solution

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. 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  Apply and Close to save your changes.
4. Run Analysis to see the results.