Division by Zero ETL Formula

Shumilex

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

GrantSmith

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

GrantSmith

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

Shumilex

@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