Division by Zero ETL error

Hello

Can someone help me with this error... I am inputting a formula in Magic ETL and am receiving 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 fine. An example of the 2 formulas I've tried below. Thanks for any insight.

IFNULL(`Total Stock`=0,0,`Total Stock`/`Conservative Max Qty per plt`)


CASE WHEN`Total Stock`=0

THEN null

ELSE (`Total Stock`/`Conservative Max Qty per plt`)

END

Best Answer

  • GrantSmith
    GrantSmith Coach
    edited November 2021 Answer ✓

    Hi @Emma

    You need to check for your denominator to be zero, not your numerator. Try this (will return 0 if `Conservative Max Qty per plt` is 0)


    CASE WHEN `Conservative Max Qty per plt` = 0 THEN 0 ELSE `Total Stock`/`Conservative Max Qty per plt` 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 November 2021 Answer ✓

    Hi @Emma

    You need to check for your denominator to be zero, not your numerator. Try this (will return 0 if `Conservative Max Qty per plt` is 0)


    CASE WHEN `Conservative Max Qty per plt` = 0 THEN 0 ELSE `Total Stock`/`Conservative Max Qty per plt` END
    
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • oh Goodness, thank you for the advice @GrantSmith