pentaho/pentaho-kettle · error · KettleException

Error calculate formula. Formula

Error message

Error calculate formula. Formula 

What it means

KettleException thrown in Formula.calcFields() when the evaluated formula returns a LibFormulaErrorValue (an error value from the Pentaho reporting formula engine) instead of a normal result. The message includes the formula, the output field, and the formula engine's error description (e.g. divide by zero, invalid argument).

Solutions

1. Wrap risky expressions in error-tolerant functions: IFERROR(...), ISNA(...), NVL(...) depending on the engine support.
2. Fix the formula's argument types/refs; read the 'error is:' tail of the message for the exact cause.
3. Guard division: use IF([denominator]=0;0;[numerator]/[denominator]).
4. Pre-convert null input fields to defaults with an upstream step or NULLIF/NVL.

Example fix

// before
"price / quantity"
// after
"IF([quantity]=0; 0; [price]/[quantity])"

// prefer formulas that guard inputs
"IF([quantity]=0; 0; [price]/[quantity])"

try { row = calcFields(row); } catch (KettleException e) { log.warn("Formula error on row: " + e.getMessage()); row[f] = null; /* or send to error stream */ }

When it happens

Trigger: Calling data.formulas[i].evaluate() yields an error value such as #VALUE!, #DIV/0!, or an invalid reference — e.g. formula references a null field, divides by zero, or uses a function with wrong arguments.

Common situations: Dividing by a field that is 0 or null on some rows; passing text where a number is expected; referencing a field name with spaces without brackets; locale issues in date parsing.

Understand the failure class

Background: "Must be a positive integer", "Invalid value", "Unsupported": the invalid-argument-value error family, when a library rejects the value you pass — this error's family across 35 libraries.

Related errors

• throw new KettleValueException( e );
• AddSequence.Exception.ErrorReadingSequence
• All input files need to have the same number of fields. File
• AutoDoc.Exception.FilenameFieldNotFound
• AutoDoc.Exception.FileTypeFieldNotFound

AI-assisted analysis of pentaho/pentaho-kettle@f3058517a1 (2026-09-13). Data as JSON: /api/errors/253787be60756528. Report an issue: GitHub.

Appendix: source

Thrown at engine/src/main/java/org/pentaho/di/trans/steps/formula/Formula.java:145

          } else {
            throw new KettleException( "Unable to find field name for formula ["
              + Const.NVL( fn.getFormula(), "" ) + "]" );
          }
        }
      }

      for ( int i = 0; i < meta.getFormula().length; i++ ) {
        FormulaMetaFunction fn = meta.getFormula()[i];
        if ( !Utils.isEmpty( fn.getFieldName() ) ) {
          if ( data.formulas[i] == null ) {
            data.formulas[i] = data.createFormula( meta.getFormula()[i].getFormula() );
          }

          // this is main part of all this step: calculate formula
          Object formulaResult = data.formulas[i].evaluate();
          if ( formulaResult instanceof LibFormulaErrorValue ) {
            // inspect why it is happens to get clear error message.
            throw new KettleException( "Error calculate formula. Formula "
                + fn.getFormula() + " output field: " + fn.getFieldName() + ", error is: " + formulaResult.toString() );
          }

          // Calculate the return type on the first row...
          // for most cases we can try to convert data on a fly.
          if ( data.returnType[i] < 0 ) {
            if ( formulaResult instanceof String ) {
              data.returnType[i] = FormulaData.RETURN_TYPE_STRING;
              fn.setNeedDataConversion( fn.getValueType() != ValueMetaInterface.TYPE_STRING );
            } else if ( formulaResult instanceof Integer ) {
              data.returnType[i] = FormulaData.RETURN_TYPE_INTEGER;
              fn.setNeedDataConversion( fn.getValueType() != ValueMetaInterface.TYPE_INTEGER );
            } else if ( formulaResult instanceof Long ) {
              data.returnType[i] = FormulaData.RETURN_TYPE_LONG;
              fn.setNeedDataConversion( fn.getValueType() != ValueMetaInterface.TYPE_INTEGER );
            } else if ( formulaResult instanceof Date ) {
              data.returnType[i] = FormulaData.RETURN_TYPE_DATE;
              fn.setNeedDataConversion( fn.getValueType() != ValueMetaInterface.TYPE_DATE );

View on GitHub (pinned to f3058517a1)