Splitting Records

Overview

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 table 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.

CustomerProduct
BobProduct 1, Product 2, Product 3... Product 10
MargretProduct 1

Target Recordset

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

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:
    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.