US and Worldwide: +1 (866) 660-7555
Results 1 to 2 of 2

Thread: Formula step

  1. #1

    Default Formula step

    I have an input stream with a field named "DATE_STRING" that is a fully-qualified timestamp with a timezone offset. I want to "clip out" the first 10 characters which would return something like "2012-05-01". I used a Formula written as DATEVALUE(LEFT("client_dt_string";10)). When I choose "Check seleted step(s)" Spoon does not indicate any error. When I run my transform however, Spoon fails and the error log includes:

    2012/05/01 17:31:49 - Formula.0 - ERROR (version 4.2.1-stable, build 15952 from 2011-10-25 15.27.10 by buildguy) : Unexpected error
    2012/05/01 17:31:49 - Formula.0 - ERROR (version 4.2.1-stable, build 15952 from 2011-10-25 15.27.10 by buildguy) : org.pentaho.di.core.exception.KettleValueException:
    2012/05/01 17:31:49 - Formula.0 - ERROR (version 4.2.1-stable, build 15952 from 2011-10-25 15.27.10 by buildguy) : org.pentaho.reporting.libraries.formula.typing.TypeConversionException: LibFormulaErrorValue{errorCode=502, errorMessage=Invalid Argument}
    2012/05/01 17:31:49 - Formula.0 - ERROR (version 4.2.1-stable, build 15952 from 2011-10-25 15.27.10 by buildguy) : LibFormulaErrorValue{errorCode=502, errorMessage=Invalid Argument}

    So, it appears I have written my formula incorrectly. Can anybody help me out with the correct syntax for this formula?
    Thanks!

  2. #2
    Join Date
    Mar 2003
    Posts
    7,986

    Default

    Fields are referenced with [fieldname]. You are trying to substring a static string named "client_dt_string", which always result in the string "client_dt" which is not exactly a parseable date.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •