Nested If in Custom Aggregation - Group Recipe

imransaiful17
imransaiful17 Registered Posts: 10 ✭✭✭✭

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.

Best Answer

  • imransaiful17
    imransaiful17 Registered Posts: 10 ✭✭✭✭
    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
    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.

Setup Info
    Tags
      Help me…