Split A Delimited Field Into Rows

[intapiuser]

Question: A data source in your ElastiCube contains a field with multiple values in it delimited by a particular character (i.e - comma, semicolon, space, etc.). You need to parse that field and create a table with a record for every value in the delimited field. For our example we will be working with a .csv file ("emails") containing only two columns - RowID and emails. The emails.email field contains multiple emails addresses delimited by a comma:

Solution: 

1. Import the table containing the delimited field into your ElastiCube as a standalone data source (meaning do not connect it to the larger data model via a relationship). This table can be hidden to prevent dashboard designers unfamiliar with the schema for using it in dashboards, and will act only as the source for our custom table. Solution is data source agnostic - so it doesn't matter whether this source is a SQL database, Excel workbook, SalesForce connector, etc
2. Create a custom table ("+ Custom" at the top of the web ElastiCube manager). For the purposes of this example the custom table will be named "emails_delimited". Paste the following code snippet:
   
   SELECT *
   FROM (
   SELECT *, 1 AS n, StrParts([email], ',', 1) AS email_split FROM [emails]
   UNION ALL
   SELECT *, 2 AS n, StrParts([email], ',', 2) AS email_split FROM [emails]  
   UNION ALL
   SELECT *, 3 AS n, StrParts([email], ',', 3) AS email_split FROM [emails]
   UNION ALL
   SELECT *, 4 AS n, StrParts([email], ',', 4) AS email_split FROM [emails]
   UNION ALL
   SELECT *, 5 AS n, StrParts([email], ',', 5) AS email_split FROM [emails]
   UNION ALL
   SELECT *, 6 AS n, StrParts([email], ',', 6) AS email_split FROM [emails]
   ) emails_delimited
   WHERE email_split IS NOT NULL AND email_split != ''
   UNION ALL
   SELECT *, 1 AS n, [email] AS email_split FROM [emails]
   WHERE [email] NOT LIKE '%,%'
3. Replace the following columns with fields applicable to your use case
   [email] = delimited field
   ',' = your delimiter (don't forget to replace in the last line of code in the snipped as well!)
   email_split = split field name
   [emails] = initial source table
4. Preview to confirm the custom table is returning as expected - in our emails example:
   
5. Note that in Step 2 - you will need a row in the SELECT clause for the maximum possible values parsing the delimited field will yield. For example in the code snippet above, there are 6 SELECT's as parsing RowID 2 yields 6 emails (RowID1 yields 3, RowID3 yields 1, etc.)