Division by Zero ETL Formula

Hi everyone,

Can someone help me with this issue... trying to input a formula in Magic ETL and I got a division by zero error. How can I rectify it? I know in some cases the divisor in my calculation will be 0 but that is okay.

Case when `ServiceName` like '%Manual_Only%' then (`InCallMin`) / ((`ReadyMin`+`InCallMin`+`NotReadyMin`+`WrapUpMin`) (`LunchMin`+`BreakTimeMin`+`TrainingMin`+`MeetingMin`)) end


Thanks for any assistance

Best Answer

  • GrantSmith
    GrantSmith Indiana 🔴
    edited August 23 Accepted Answer

    Hi @Shumilex

    Looks like an operator is missing between `WrapUpMin`) and (`LunchMin`

    What should that operator be? You'll need to utilize your CASE Statement to check for your denominator value being 0 prior to running the calculation with your denominator.

    CASE WHEN `ServiceName` like '%Manual_Only%' THEN
      CASE WHEN ((`ReadyMin`+`InCallMin`+`NotReadyMin`+`WrapUpMin`)(`LunchMin`+`BreakTimeMin`+`TrainingMin`+`MeetingMin`)) = 0 THEN
          0 -- Or whatever value you want when the denominator is 0
      ELSE 
         (`InCallMin`) / ((`ReadyMin`+`InCallMin`+`NotReadyMin`+`WrapUpMin`)(`LunchMin`+`BreakTimeMin`+`TrainingMin`+`MeetingMin`))
      END
    END
    

Answers

  • @GrantSmith


    Thank you. I did the below as well, both seem to give the same results:


    case 

    when `ServiceName` like '%Manual_Only%' then (`InCallMin`)/ NULLIF(((`ReadyMin`+`InCallMin`+`NotReadyMin`+`WrapUpMin`) -(`LunchMin`+`BreakTimeMin`+`TrainingMin`+`MeetingMin`)),0)

    End