ETL error: "failed to convert value 'Infinity' from type 'Floating Decimal' to type 'Fixed Decimal'"

jrtomici · 2023-02-13T21:32:08+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

Magic ETL

Error

Formulas

Accepted answers

GrantSmith

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?

All comments

GrantSmith

jrtomici

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?

BraydenJ

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!

jrtomici

@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

rco

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 becomes DECIMAL(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 of 100 / maybe_zero you'd write 100 / 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 the FINITE(…) function. FINITE(num) is true when num is a real number, null if it is null, and false if it is any non-finite special number like NaN or Infinity. If you want to use a float column num without its NaNs or Infinities, you can wrap it in an expression like this: CASE WHEN FINITE(num) THEN num ELSE NULL END