Nested If in Custom Aggregation - Group Recipe
Hi DataIKU,
I am having problem to obtain results from these logics using Custom Aggregation in the Group Recipe.
What I have is a table with columns : actual_compl_year, actual_compl_month, process_date_year, process_date_month, minus_date1, minus_date2.
So what I am trying to achieve is that,
If (actual_compl_year == process_date_year && actual_compl_month == process_date_month) then sum(case when minus_date1 IS NOT NULL THEN minus_date1 ELSE minus_date2)
To elaborate that exactly, if both year and months are same, then we go to add the minus_date1 of all rows, but if minus_date1 is empty we take minus_date2.
Best Answer
-
I have got the solution to this, SUM( CASE WHEN `actual_compl_year` == `process_date_year` and `actual_compl_month` == `process_date_month` THEN (case WHEN `minus_date1` IS NOT NULL THEN `minus_date1` ELSE `minus_date2` END) WHEN `actual_compl_year` != `process_date_year` and `actual_compl_month` != `process_date_month` THEN NULL ELSE NULL END ) You may close this discussion.
Answers
-
Manuel Alpha Tester, Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Dataiku DSS Adv Designer, Registered Posts: 193 ✭✭✭✭✭✭✭It is not clear what your other group settings and aggregations are, but one suggestion is:
- In a previous prepare recipe, using the Formula processor, create a column minus_datex that implements your logic if minus_date1 IS NOT NULL THEN minus_date1 ELSE minus_date2.
- Then in the Group recipe you can simple sum minus_datex
- One way to implement your conditions "both year and months are same" is add them to the pre-filter.
As I wrote, all of this depends on whatever else you are doing on the Group recipe.