Division by Zero ETL Formula

Options
Shumilex
Shumilex Member
in Charting & Analyzer

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 Coach
    edited August 2021 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
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    edited August 2021 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
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Shumilex
    Shumilex Member

    @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

Categories