cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

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:

Community_Admin_0-1634387569837.png

Answer

  • 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
  • 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 '%,%'
  • 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
  • Preview to confirm the custom table is returning as expected - in our emails example:
    Community_Admin_1-1634387585051.png

     

  • 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.)



Version history
Last update:
‎10-16-2021 05:33 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request