Nested If in Custom Aggregation - Group Recipe

[imransaiful17]

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.

sum(case when `actual_compl_year` == `process_date_year` and `actual_compl_month` == `process_date_month` 

and `minus_date1` IS NOT NULL THEN `minus_date1` ELSE `minus_date2` END) 

i have these for now but i know it is not correct because if NULL it will take the minus_date2 and i believe NESTED IF or CASE will work on this. But could not find the correct way to deliver this.

[Manuel]

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.

[imransaiful17 · Accepted solution]

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.