KNOWLEDGE BASE

Creating Views When Data is Formatted as Text Lists


Published: 23 May 2018
Last Modified Date: 03 Jan 2019

Question

How to create a view using data is structured as text, (or string), lists rather than a row for every possible value.

This article contains two examples: Making a word cloud of pet names, but each row in the underlying data lists all of the pets in one household as a comma separated list. Or finding the average of pet ages per household where every row contains a comma separated list of ages.

Similar to the table below:
                                         
Household IDPet NamesPet Age
1Rocky, Doug, Spot1, 6, 10
2Spot, Whiskers4, 5
3Rocky, Spot, Mittens2, 4, 6
4Minnie, Mickey1, 1
5Spot12
6Bella, Rosa7, 9
7Whiskers, Mittens, Snowball3, 2, 1
8Snowball, Cloudy, Spot7, 8, 2
9Spot9
10Spot, Rocky2, 1
11Spot7
12Spot, Rocky2, 5

 

Environment

  • Tableau Desktop
  • Tableau Prep (optional)

Answer

Option 1: Using Tableau Prep and Tableau Desktop

The attached "split then pivot prep flow.tfl" and "word cloud.twbx" demonstrate the following directions:
Step 1 - Clean the data in Tableau Prep
  1. Open Tableau Prep and connect to the original data source
  2. Hover over Original Data, click the plus, and select Add Step
  3. In Clean 1, right-click the field containing the list of values, this examples uses [Pet Name], and select Split Values > Custom Split…
  4. In the Custom Split dialog, do the following and click Split
    • Enter the separator, which is ,
    • For Split off, select All
  5. Hover over Clean 1, click the plus, and select Add Pivot
  6. Drag [Pet Name - Split 1], [Pet Name - Split 2], and [Pet Name - Split 3] to the Pivot1 Values column
  7. Hover over Pivot 1, click the plus, and select Add Output
  8. In Output, run the flow
Step 2 - Build the view as normal in Tableau Desktop
For directions on creating a word cloud, see Creating a Word Cloud. For directions on creating an average, see Data Aggregation in Tableau


Option 2: Create word cloud only using Tableau Desktop

Use the Tableau Desktop built-in custom split function to split the list of pet names, and create a duplicate of the data source for each split field. Then create a master pet name list, and join the master pet list to each duplicate data source on a different split field. The attached workbook uses sample data to demonstrate these instructions.
Step 1 - Split the strings
  1. Connect to the "Original Data" sheet of the attached Excel file
  2. In the Data Source tab, right-click [Pet Names] and select Custom Split…
  3. In the Custom Split dialog, do the following and click OK:
    1. For Use the separator, enter "," without quotes
    2. For Split off, select All
Step 2 - Create a duplicate of the data source for each split field
  1. Navigate to a worksheet
  2. Navigate to Data > Original Data (string to split) > Duplicate
  3. Repeat step 5
Step 3 - Join master pet list
  1. Connect to the "Master List" sheet of the attached Excel file
  2. Navigate to Data > Edit Relationships…
  3. In the Relationships dialog, so the following
    1. For Primary data source, select Master List (string to split)
    2. For Secondary data source, select Original Data (string to split)
    3. Select the Custom radio button
    4. Click Add…
    5. In the Add/Edit Field Mapping dialog select Pet Names Master and Pet Names - Split 1, and click OK
    6. For Secondary data source, select Original Data (string to split) (copy)
    7. Select the Custom radio button
    8. Click Add…
    9. In the Add/Edit Field Mapping dialog select Pet Names Master and Pet Names - Split 2, and click OK
    10. For Secondary data source, select Original Data (string to split) (copy 2)
    11. Select the Custom radio button
    12. Click Add…
    13. In the Add/Edit Field Mapping dialog select Pet Names Master and Pet Names - Split 3, and click OK
Step 4 - Create a calculation and build the view
  1. Create a calculated field with a name like "Word Size" with a calculation similar to the following:
    	ZN(SUM([Original Data (string to split)].[Number of Records]) )
    	ZN(SUM([Original Data (string to split) (copy 2)].[Number of Records]))
    	ZN(SUM([Original Data (string to split) (copy)].[Number of Records]))
  2. Drag [Pet Names Master] to Text on the Marks card
  3. Drag [Word Size] to Size on the Marks card
  4. In the dropdown on the Marks card, select Text
 

Option 3: Create an average only using Tableau Desktop

Step 1 - Split the strings
  1. Connect to the "Original Data" sheet of the attached Excel file
  2. In the Data Source tab, right-click [Pet Age] and select Custom Split…
  3. In the Custom Split dialog, do the following and click OK:
    1. For Use the separator, enter "," without quotes
    2. For Split off, select All
Step 2 - Create a calculation and build the view
  1. Right-click [Pet Age - Split 1] in the data pane and select Change Data Type > Number (whole)
  2. Right-click [Pet Age - Split 1] in the data pane and select Convert to Measure
  3. Repeat steps 1-2 for all split fields
  4. Create a calculated field with a name like "Avg Pet Age per Household" with a calculation similar to the following:
    SUM( ZN([Pet Age - Split 1])
    + ZN([Pet Age - Split 2])
    + ZN([Pet Age - Split 3]) )
    /
    ( COUNT([Pet Age - Split 1])
    + COUNT([Pet Age - Split 2])
    + COUNT([Pet Age - Split 3]) )
  5. Drag [Avg Pet Age per Household] to Text on the Marks card
  6. Drag [Household ID] to the Rows shelf

Additional Information

Option 2 may cause performance issues due to having to duplicate the original data source. To reduce the impact, consider making a data source just for the word cloud view before duplicating the data source, and make that data source as small as possible.

The size of a data source can be reduced by removing any tables whose fields are not being used, and adding data source filters. If using an extract, click the Hide All Unused Fields button in the Extract Data dialog.

Discuss this article... Feedback Forum
Did this article resolve the issue?