pentaho/pentaho-kettle · error · KettleStepException

Error converting data while looking up value

Error message

Error converting data while looking up value

What it means

When the main-stream lookup key's data type differs from the lookup-stream key type, the step attempts convertDataCompatible(); if that conversion fails (KettleValueException) it is wrapped as 'Error converting data while looking up value'. This means incompatible data, not just differing declared types (e.g. converting non-numeric String to Integer).

Solutions

  1. Align the key field data types upstream (e.g. add 'Field exists in stream?' / Select Values metadata-type conversion) before the lookup
  2. Use a 'String Operations' or 'Calculator' step to normalize/trim/standardize key values so conversion succeeds
  3. Verify date formats and number formats match between the two streams
  4. Pre-clean the data (remove empty/non-numeric keys) with a Filter or JavaScript step

Example fix

// before: keys String vs Integer mismatch
// after: add Select Values step converting 'id' String->Integer before StreamLookup
<meta type="Integer" name="id"/>
Defensive patterns

Strategy: try-catch

Validate before calling

ValueMetaInterface in = mainRowMeta.getValueMeta(mainIdx);
ValueMetaInterface lk = lookupRowMeta.getValueMeta(lookupIdx);
if (in.getType() != lk.getType() && !in.isCompatibleNumeric(lk)) {
  throw new IllegalArgumentException("Key types differ: " + in + " vs " + lk);
}

Type guard

boolean sameType(ValueMetaInterface a, ValueMetaInterface b) {
  return a != null && b != null && a.getType() == b.getType();
}

Try / catch

try { lookupValues(...); } catch (KettleStepException e) {
  if (e.getMessage().contains("Error converting data")) {
    log.error("Key value unconvertible; check raw key data", e); setErrors(1L);
  }
  throw e;
}

Prevention

When it happens

Trigger: lookupValues detects inputValue.getType() != lookupValue.getType(), calls lookupValue.convertDataCompatible(inputValue, lu[i]), which throws because the data cannot be converted (e.g. 'abc' to Integer, malformed Date string).

Common situations: Key field is String in one stream and Integer in the other with unparseable values; date keys with differing date formats; nulls or locale-specific number formats in the key column.

Understand the failure class

Background: Type mismatch errors: IllegalArgumentException, TypeError and type guards across 150 open-source libraries — this error's family across 150 libraries.

Related errors

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

Appendix: source

Thrown at engine/src/main/java/org/pentaho/di/trans/steps/streamlookup/StreamLookup.java:259

      if ( data.convertKeysToNative[i] ) {
        lu[i] = data.lookupMeta.getValueMeta( i ).convertBinaryStringToNativeType( (byte[]) row[data.keynrs[i]] );
      } else {
        lu[i] = row[data.keynrs[i]];
      }
    }

    // Handle conflicting types (Number-Integer-String conversion to lookup type in hashtable)
    if ( data.keyTypes != null ) {
      for ( int i = 0; i < data.lookupMeta.size(); i++ ) {
        ValueMetaInterface inputValue = data.lookupMeta.getValueMeta( i );
        ValueMetaInterface lookupValue = data.keyTypes.getValueMeta( i );
        if ( inputValue.getType() != lookupValue.getType() ) {
          try {
            // Change the input value to match the lookup value
            //
            lu[i] = lookupValue.convertDataCompatible( inputValue, lu[i] );
          } catch ( KettleValueException e ) {
            throw new KettleStepException( "Error converting data while looking up value", e );
          }
        }
      }
    }

    Object[] add = null;

    if ( data.hasLookupRows ) {
      try {
        if ( meta.getKeystream().length > 0 ) {
          add = getFromCache( data.cacheKeyMeta, lu );
        } else {
   // Just take the first element in the hashtable...
          throw new KettleStepException( BaseMessages.getString( PKG, "StreamLookup.Log.GotRowWithoutKeys" ) );
        }
      } catch ( Exception e ) {
        throw new KettleStepException( e );
      }

View on GitHub (pinned to f3058517a1)