GROUP RECIPE - Custom aggregation

lnguyen

Hi,

I have a table with structure similar to this:

ID	date start	date end	contract_nb	tag_subscription
123	30/01/2015	30/02/2015	1	first subscription
123	30/01/2020	30/06/2020	2	re-subscription
123	30/01/2022	30/06/2022	3	addition
123	30/01/2022	30/06/2022	3	re-subscription
456	30/01/2015	30/02/2015	1	first subscription
456	30/01/2020	30/06/2020	2	re-subscription
456	30/01/2020	30/06/2020	2	re-subscription
456	30/01/2020	30/06/2020	2	addition
456	30/01/2022	30/06/2022	3	re-subscription

So now I want to use a GROUP RECIPE to creat a dataset with one row for each ID:

I want to create a custom aggregation which, for each id, counts the number of distinct "contract_nb" where "tag_subscription" equal to "re-subscription" but I can't make it work.

Can someone help me to find the right custom aggregation code to achieve this?

As you can see I must count the "contract_nb" because it's unique for one action of subscription which can contain several tags like "addition" or "re-subsctiption" and like in the case of id 456, one action can have 2 "re-subcription" row.

The end result should look like this:

ID	min date start	max date end	number of re-subscription
123	30/01/2015	30/06/2022	2
456	30/01/2015	30/06/2022	2

Thanks a lot in advance

---

Operating system used: Window

NN

Hi @lnguyen
,

In the group recipe have you been able to look at the Computed Columns and Custom Aggregations options?

In the computed columns section you can create a new column using a dss formula

if(tag_subscription=='re-subscription',contract_nb,null)

and in the next group step you can take a distinct of this new column that you created.

The second option you can try in you can add a custom aggregation using a simple case when expression in the custom aggregation step of the group recipe

count(distinct (case when "tag_subscription"='re-subscription' then "contract_nb" end))

Hope i have understood your question correct.

lnguyen

Hi @NN
,

It's exactly what I was looking for.

I knew the code is simple but my SQL is rusty so your help is very much appreciated.

Thanks again,

Have a nice day,