Now, let's take it one step further: what if we want to create an aggregation based on multiple fields? As we saw in the previous article, the “for each…” section only allows you to select one field… let’s take a look at how it can be done!
Let’s take the example of counting the number of customers per Gender AND Country
To be able to select a combination of multiple fields to create an aggregation upon, we’d first need to combine those fields as a new one, so we can later use it when building the Aggregation.
- Create a Step1 Selection with a Custom Value in the Target Definition that is using SQL function Concatentating Country field with Gender field.
As a result of this first step, we will have a Custom Value field added to the Target DE, which has a unique value field, combining the fields that we need to use for our aggregation:
- Create a Step2 Selection, in which the Selection Criteria is the Target DE of Step1.
In the Target Definition of Step2 Selection, and given our example “counting number of customers per Gender and Country” we need the following fields to be added to our Target Data Extension
- Country: (From the Selected DE)
- Gender: (From the Selected DE)
- Count: Custom Value, in which we are going to create our Aggregation, to count customers for each Country AND Gender.
The result will be the Sum of CustomerId in the DESelect_Demo_Customers for each Country-Gender in Step1
While we got the correct results in the preview, they are duplicated (one row per CustomerId, even though the CustomerId column is not shown). So, one more step to remove the duplicates could be:
Making both “Country” and “Country-Gender” a primary key
Finally, the result will be what we are looking for: