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>
2
Categories
 All Categories
 1.8K Product Ideas
 1.8K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 299 Workbench
 6 Cloud Amplifier
 8 Federated
 2.9K Transform
 100 SQL DataFlows
 616 Datasets
 2.2K Magic ETL
 3.8K Visualize
 2.5K Charting
 730 Beast Mode
 55 App Studio
 40 Variables
 682 Automate
 175 Apps
 451 APIs & Domo Developer
 46 Workflows
 10 DomoAI
 34 Predict
 14 Jupyter Workspaces
 20 R & Python Tiles
 394 Distribute
 113 Domo Everywhere
 275 Scheduled Reports
 6 Software Integrations
 121 Manage
 118 Governance & Security
 8 Domo Community Gallery
 38 Product Releases
 10 Domo University
 5.4K Community Forums
 40 Getting Started
 30 Community Member Introductions
 108 Community Announcements
 4.8K Archive