New to Dataiku DSS? Try out our NEW Quick Start Programs today and get onboarded on the product in just one hour! Let's go

Nested If in Custom Aggregation - Group Recipe

Solved!
imransaiful17
Level 2
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.

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.
0 Kudos
1 Solution
imransaiful17
Level 2
Author
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.

View solution in original post

0 Kudos
2 Replies
Manuel
Dataiker
Dataiker

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.

0 Kudos
imransaiful17
Level 2
Author
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.

View solution in original post

0 Kudos
A banner prompting to get Dataiku DSS