Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
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
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.
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.
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,