Multivalue Split in Tableau CRM aka Einstein Analytics

overview

5 min readOct 16, 2021

As you may know, we do not have a straight functionality in Dataflow to perform multi-value split/transformation. fortunately, Data Prep 3.0 is in place to save us. we do need to create some steps which can be a larger exercise based on the multi-picklist value.

Step 1 :- To check the Column profile

Column Details: Click on the lens icon or double click on the column header which is the column profile. it has 2 section profiles & Attributes. profile section shows the details of the sample data like Valid Values, Missing Values, Zeros, and a histogram of the values. Attributes section shows the API Name of the column/field, the type of column(Measure or Dimension), and multiple value indicator when applicable.

Step 2:- Conversion of Multi value field into string Type.

In order to perform any transformation operations on the multi-value field then it has to be converted into the String type(just see below ).

<iframe title=”vimeo-player” src=”https://player.vimeo.com/video/633782287?h=45661efb15" width=”640" height=”300" frameborder=”0" allowfullscreen></iframe>

Options Available for Multivalue transformations:-

* Power of one

(The Power of One is a Salesforce custom formula field that is proclaimed to be ‘The greatest formula ever written’, adding an extra boost to your Salesforce analytics)

*Split transformation

product list:-

Step 3:- Transformation of Multi-value field by using the power of one concept.

Once the multi value field has been converted into string type.

2. In a Transform node of a Data Prep recipe, click the fx which is the formula field the Preview tab. then click the custom Formula.

3. The absolutely easiest way to leverage power of one is by hardcoding or using Case statement formula. Select a functions, then serach the case keyword,click the plus sign. This adds the function to the right side of the formula pane along with a snippet on what all parameters are required to use this function.

4. inside the case functions we will leverage the contains() function. This function allows us to returns 1/true if the given string contains the specified substring.

5. Select the appropriate Output Type.

6. Specify the column labels for columns. its advisable to use the same “search substring” name as column name to calculate.

7. Click Apply to add the transformation to the Transform node.

8. we need to continue to create another 7 columns with different “search substring” since in this case we have 8 products. (because there may be chances one account can have 8 products as primary . It will be a little time consuming, but atleast we have this option to perform the split opearations on multivalue field.)

Power of One: Accounts

The below is not a valid business use case. for example purpose i taken this.

The scenario:

In your Account, you want to show the primary products of accounts that have been ‘assigned’ to accounts and how each product is performing among the Accounts.

The solution:

You will see this in the screenshot below, where there were GenWatt Diesel product is choice of 11 Accounts and on the other hand Burlington Textiles Corp of America purchasing only two products .

Step 4:- Transformation of Multi-value field by using the Split transformation

Once the multi value field has been converted into string type.

2. In a Transform node of a Data Prep recipe, select the converted column in the Preview tab. then click the split button.

3. Select the delimiter in the Delimiter field( if you are not sure about the delimiter then connect the output node and see on the lens visulization)

in this case , primary products are separated by a comma in the converted primary product field, so specified comma as the delimiter.

4. see below screenshot , the primary product field is split into two. The Split transformation splits the value at the first occurrence of the delimiter. In this case, the first comma appears after the “GenWatt Diesel ”. So the 4 products is broken into the “GenWatt Diesel” and the rest (“GenWatt Gasoline”, “GenWatt Propane”, and “Installation: Industrial — High”).

5. add the Split transformation again to further split the “primary product string split 2” column . it will split the “GenWatt Gasoline” as the first split and rest (“GenWatt Propane”, and “Installation: Industrial — High”)of the values as the second split.

7. we need to continue the split the transformation no of picklist values available for the Analysing field. (Now in my example, I have just taken 8 products as a values, same has been listed above. to finish splitting we need to perform split opearations on 8 times because there may be chances one account can have 8 products as primary . It will be a little time consuming, but atleast we have this option to perform the split opearations on multivalue field.)

8. since we don’t need the original column and every split transformation 2nd split column that we split, add the Drop Columns transformation after the Split transformation to drop it from the recipe. From that point, the original column doesn’t appear in Preview anymore.

please refer the below video