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.
Customer | Product |
---|---|
Bob | Product 1, Product 2, Product 3... Product 10 |
Margret | Product 1 |
Target Recordset
Customer | Product |
---|---|
Bob | Product 1 |
Bob | Product 2 |
Bob | ... |
Bob | Product 10 |
Margret | Product 1 |
Solution
- Open the properties of the Table where we will be splitting the records
- 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 )
- Click Apply and Close to save your changes.
- Run Analysis to see the results.