GROUP RECIPE - Custom aggregation
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
Best Answer
-
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.
Answers
-
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,