SAQL simply explained – Part 2

In the first part of this blog series, I explained what is SAQL and the anatomy of SAQL. This will be the foundation for the rest of this series. The SAQL query we covered was simply, in fact, there was no reason to write that query in SAQL, which I also mentioned isn’t recommended for that use case. In this second part, we will take the basic query to the next level and look at how does it looks with multiple grouping and measures. Again I will highlight that this is still not a use case to switch SAQL, but it is a component you need for advanced use cases.

Extending our query

Let’s first have a look back to the query we created in part 1. We grouped by Stage and chose sum of Amount as the measure.

1 q = load "opportunities";

2 q = group q by 'StageName';

3 q = foreach q generate 'StageName' as 'StageName', sum('Amount') as 'sum_Amount';

4 q = order q by 'StageName' asc;

5 q = limit q 2000;

Multiple groupings

Now, what if we want to see the sum of Amount per Stage for each Account Type? Well, we can add multiple groupings in our query. Obviously, it’s easier to do it from the UI, but let’s have a look at how it will look in SAQL.

1 q = load "opportunities";

2 q = group q by ('StageName', 'Account.Type');

3 q = foreach q generate 'StageName' as 'StageName', 'Account.Type' as 'Account.Type', sum('Amount') as 'sum_Amount';
4 
q = order q by ('StageName' asc, 'Account.Type' asc);

5 q = limit q 2000;

As you can see above we have two things happening. The first one is in our ‘group’ statement where we have added the dimension ‘Account.Type’. Since we have two groupings in this query we do need to add those in a pair of parentheses and separate the two groupings with a comma. We can, of course, add more groupings if we wish.

If you think back to the first part of this blog series, we might have added a ‘group’ statement, but that doesn’t mean that grouping gets projected in the query. In order for us to have the grouping displayed in a table or chart, we need to include the field(s) from the ‘group’ statement in the ‘foreach’ statement. Hence you will see in line 3 that we are generating the Stage as well as the Account Type field and each field is separated by a comma. Note you will not be able to project a dimension that you have not used in your ‘group’ statement.

Also notice that in line 4 I have added the second field in my ‘order’ statement. Just as with the ‘group’ statement I have added parentheses around the two fields to use for sorting. The ‘order’ statement will be covered more in detail below.

Multiple measures

Now, what if we wanted to add one more measure? Well, this will be a bit more simple. Measures just need to be added to the ‘foreach’ statement. When a measure is being called you need to define what aggregation you want to apply to the grouped fields, you of course also need to define the field or measure you are using in your aggregation. Let’s look at an example where we also include the average of Amount.

1 q = load "opportunities";
2 q = group q by ('StageName', 'Account.Type');
3 q = foreach q generate 'StageName' as 'StageName', 'Account.Type' as 'Account.Type', sum('Amount') as 'sum_Amount', avg('Amount') as 'avg_Amount';
4 q = order q by ('StageName' asc, 'Account.Type' asc);
5 q = limit q 2000;

In line 3 you will notice we have added the aggregate keyword ‘avg’ and set the measure as Amount – avg(‘Amount’). The second part is how we want to display this aggregation in the query, in other words, the alias. With the groupings we have just called the exact same name so we can use the XMD settings (read about XMD here). While we for measures are using the same API name we are also adding the aggregation in front of it. Now, this is not mandatory to do. But by adding the aggregation function we are telling Einstein Analytics to display the aggregation in the table or chart – it now says “Avg of Amount” and not just “Amount”.

I will not go over every single aggregation function you can use, so if you are wondering which ones you can apply to your query then check out the documentation.

Ordering

The query we have worked with defaults to sorting by the dimension in ascending order. As mentioned above adding two groupings and switching to SAQL would add both dimensions in the order statement sorting ascending. However, as mentioned in part 1 of this blog series the ‘order’ statement is not necessary. In can be deleted, but it is best practice if you want to ensure correct ordering. Also if you are writing your ‘order’ statement yourself you do not need to add both dimensions, one would be enough, of course, as long as that field exists in your query.

You can sort descending by switching ‘asc’ out with ‘desc’ like below. You can have a combination of ascending and descending sorting as well.

q = order q by ('StageName' desc, 'Account.Type' desc);

So far we have been looking at dimensions, but you can of course order ascending or descending on measures as well. You will need to pick the alias from the ‘foreach’ statement and of course, define if you want an ascending or descending order. You can see an example below.

q = order q by 'sum_Amount' desc;

Technically you can add two measures in your ‘order’ statement, however, it does not make sense to do this, as you cannot sort on two values at the same time. Hence, the first defined measure will win in terms of sorting.

The basics are covered

With some more basic SAQL knowledge covered you should be able to look at a query and determine what the result would look like. Next time we will look at filters and how they can be applied to the SAQL query.

Written by