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>
0
Categories
 All Categories
 1.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 285 Workbench
 4 Cloud Amplifier
 5 Federated
 2.7K Transform
 91 SQL DataFlows
 577 Datasets
 2.1K Magic ETL
 3.5K Visualize
 2.4K Charting
 619 Beast Mode
 26 App Studio
 33 Variables
 609 Automate
 155 Apps
 421 APIs & Domo Developer
 31 Workflows
 2 DomoAI
 29 Predict
 12 Jupyter Workspaces
 17 R & Python Tiles
 372 Distribute
 103 Domo Everywhere
 266 Scheduled Reports
 3 Software Integrations
 100 Manage
 97 Governance & Security
 4 Domo University
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 91 Community Announcements
 4.8K Archive