My most used computeExpression

One thing I cannot control in the Einstein Analytics UI is the sorting of the values. It’s either alphabetically or descending/ascending values, which do make sense. Now, most of the times that’s all good, just not when you are dealing with values that happen in a certain order. As an example, I can mention opportunity stage and weekdays, where you expect a certain order and if that order is not followed you end up causing confusion because users have to spend extra time decoding what they are looking at.

If you are pulling data in from Salesforce then the solution is actually quite simple; it’s dataflow and computeExpression. While it sounds complex, it’s just about adding another node (step) to your dataflow. Okay, let’s take a step back… when you create a dataset in Einstein Analytics you choose a root object, your fields and any relationships (other related objects) you want to include. This is very simple to do in the UI. But behind the scenes a data flow is created with a set of nodes; sfdcDigest to extract an object, augment to join extracted objects and sfdcRegister to generate the actual dataset in Einstein Analytics. It should look something like this:

So how does this help you? Well by adding a computeExpression we can calculate a new field. You can use many different functions in this computeExpression, if you are interested in which, then have a look here. But in order to control sorting, you can use a simple ‘case statement’. And as the title of this blog says this is my most used computeExpression.

The very first thing we have to do is, of course, find the dataflow, so in the top right corner click the gear icon and then the data manager. The data manager is the go-to place for anything, yes you guessed it, data! In the menu select “Dataflow & Recipes” and then click on the dataflow that holds the dataset you want to change.

Next, the computeExpression should be added so click on the fx symbol.

There are two things that immediately need to be completed; the name of the node and the source. The name can be anything, but I find it best to call it something that describes what it does. I will call mine computeExpression_Sorting. The source is referring to the node that comes just before. I tend to do my computeExpression after my augments and of course before the sfdcRegister, so in my case, I’ll have the augment_User node as the source node.

We now need to create the actual field and value of it. Note you can have multiple fields in one node, so if you have a need for multiple computeExpressions no need to create multiple nodes. Click “+ Add Field”. Give your new field a name (that will be your API name) and the label will be automatically added, but you can, of course, change it. I will just call mine StageSorted and Stage (sorted). Then you need to choose your field type (text, number or date), which of course depends on what output you wish. Here I’ll keep it as text.

The last step of the computeExpression is to create your SAQL Expression. As mention above we will do a ‘case statement’. Here you need to know the input field as well as the expected values. A case statement is similar to the ‘if statement’ in Salesforce’s formula field. The syntax is like this:

case
when 'API_Name' operator "Field Value 1" then "New Field Value 1"
When 'API_Name' operator "Field Value 2" then "New Field Value 2"
...
else "Field Value 6"
end

In my scenario of getting the correct sorting for my stage field it will look like this:

case 
when 'StageName' == "Prospecting" then "01. Prospecting" 
when 'StageName' == "Qualification" then "02. Qualification" 
when 'StageName' == "Needs Analysis" then "03. Needs Analysis" 
when 'StageName' == "Value Proposition" then "04. Value Proposition" 
when 'StageName' == "Id. Decision Makers" then "05. Id. Decision Makers" 
when 'StageName' == "Perception Analysis" then "06. Perception Analysis" 
when 'StageName' == "Proposal/Price Quote" then "07. Proposal/Price Quote" 
when 'StageName' == "Negotiation/Review" then "08. Negotiation/Review" 
when 'StageName' == "Closed Won" then "09. Closed Won" 
when 'StageName' == "Closed Lost" then "10. Closed Lost" 
else "11. Other" 
end

Now you can click “Save”. If you want to create more fields in your computeExpression you can by using the “+ Add Field” or click on “Create”.

As you can see the new node is not acting as a source to our sfdcRegister meaning it is not actually a part of the dataset generated in this dataflow. At least not yet.

All we have to do is click on the right arrow on the computeExpression node and drag it to the sfdcRegister node, in my case the register_Oppty_Account.

Once done you can click on “Update Dataflow” in the top right corner and you are done. Well, don’t forget to run your dataflow if you want to see the result immediately, else your dataset will update at the next scheduled run.


Leave a Reply

Your email address will not be published. Required fields are marked *