ETL error: "failed to convert value 'Infinity' from type 'Floating Decimal' to type 'Fixed Decimal'"
Hi,
I have a magic ETL dataflow. It has 2 input sources. There's a formula tile where we calculate a pacing percentage.
(`Delivered Impressions` / (`Campaign Flight`  `Days Remaining`)) / (`Total Goal` / `Campaign Flight`)
We've been using this dataflow and formula without any issue for months and now we're getting this strange error "failed to convert value 'Infinity' from type 'Floating Decimal' to type 'Fixed Decimal'" causing the run to fail. It took a while to even find the source of the error since all the formulas within the tile validated just fine and the ETL interface didn't specify which column was causing the problem. I also examined the source input datasets and didn't notice any fields out of the ordinary.
Is there a way to easily debug this, or perhaps is there a known fix for this error? Thanks.
Best Answer

Typically when you're getting an Infinity error it means you're dividing by 0. Have you tried wrapping your formula in a CASE statement to check against having a 0 denominator and then return a 0 or some other default value?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers

Typically when you're getting an Infinity error it means you're dividing by 0. Have you tried wrapping your formula in a CASE statement to check against having a 0 denominator and then return a 0 or some other default value?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 
Hi, just bumping this as a new error has come up. Now I'm getting an error for this formula tile that says "failed to convert value 'NaN' from type 'Floating Decimal' to type 'Fixed Decimal'"
It's my understanding that this is “not a number” but how would I go about debugging this?
0 
I receive the same error message "failed to convert value 'Infinity' from type 'Floating Decimal' to type 'Fixed Decimal'" when I use a dynamic unpivot where I don't believe any math or formula is occurring that would cause a div/0 error. Any suggestions here? Thanks!
0 
@BraydenJ I don't quite remember how I solved for this, but I believe I ended up opting to do the calculation as a beast mode instead of using ETL. Good luck
0 
I'll just add some context for anybody still watching this issue. This comment is specific to Magic ETL; Beast Mode (Domo Adrenaline) works differently:
Issues like this happen when you mix floats and decimals, or sometimes integers and decimals (since dividing an integer by an integer produces a float). When a decimal is divided by zero, you get a "division by zero" error. A float, in contrast, becomes one of three special values depending on the numerator: Infinity, Infinity, or NaN for numerators 1, 1, and 0 respectively.
Almost any function or operator applied to a decimal and a float will begin by trying to convert the float to a decimal (e.g.DECIMAL(8) / 8.0
becomesDECIMAL(8) / DECIMAL(8.0)
). If the float is any of those aforementioned special values this conversion fails with the error you saw.
There are a couple ways to avoid this problem. The most common is to ensure that any denominator that might be zero becomes null instead, so any division by zero results in null instead of an error or a NaN/Inf/Inf. For example, instead of100 / maybe_zero
you'd write100 / NULLIF(maybe_zero, 0)
. This solution works for both floats and decimals as long as you apply it consistently.
However, if you already have NaN/Inf/Inf in a float column, you need a way to get rid of it or at least treat it differently in your formula. To do that, you can use theFINITE(…)
function.FINITE(num)
is true whennum
is a real number, null if it is null, and false if it is any nonfinite special number like NaN or Infinity. If you want to use a float columnnum
without its NaNs or Infinities, you can wrap it in an expression like this:CASE WHEN FINITE(num) THEN num ELSE NULL END
Randall Oveson <randall.oveson@domo.com>
1
Categories
 All Categories
 1.6K Product Ideas
 1.6K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 288 Workbench
 4 Cloud Amplifier
 7 Federated
 2.8K Transform
 94 SQL DataFlows
 591 Datasets
 2.1K Magic ETL
 3.6K Visualize
 2.4K Charting
 663 Beast Mode
 39 App Studio
 34 Variables
 641 Automate
 163 Apps
 433 APIs & Domo Developer
 42 Workflows
 3 DomoAI
 31 Predict
 12 Jupyter Workspaces
 19 R & Python Tiles
 378 Distribute
 107 Domo Everywhere
 266 Scheduled Reports
 5 Software Integrations
 108 Manage
 105 Governance & Security
 8 Domo University
 23 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 95 Community Announcements
 4.8K Archive