Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Formula, where to get more info

  1. #1
    Join Date
    Sep 2011
    Posts
    171

    Default Formula, where to get more info

    Hello Everyone,

    I am trying to use formula and getting error casting (see below). I wonder where I can get more information on how to use formula component?

    I attached the transformation.

    If([InstrumentType]<>"Cash equivalent";[Valuation Date]; [Valuation Date])

    Regards,
    Alex


    2011/10/12 16:06:32 - Spoon - Transformation opened.
    2011/10/12 16:06:32 - Spoon - Launching transformation [SetDatePutEverythingToExcel]...
    2011/10/12 16:06:32 - Spoon - Started the transformation execution.
    2011/10/12 16:06:33 - SetDatePutEverythingToExcel - Dispatching started for transformation [SetDatePutEverythingToExcel]
    2011/10/12 16:06:33 - Transformation metadata - Natural sort of steps executed in 0 ms (5 time previous steps calculated)
    2011/10/12 16:06:33 - Table output.0 - Connected to database [iris_staging] (commit=1000)
    2011/10/12 16:06:33 - MainRows Excel Input.0 - Finished processing (I=3, O=0, R=0, W=3, U=0, E=0)
    2011/10/12 16:06:33 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Unexpected error
    2011/10/12 16:06:33 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleValueException:
    2011/10/12 16:06:33 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : java.lang.ClassCastException: java.lang.Boolean cannot be cast to java.util.Date
    2011/10/12 16:06:33 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : java.lang.Boolean cannot be cast to java.util.Date
    2011/10/12 16:06:33 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/10/12 16:06:33 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.formula.Formula.calcFields(Formula.java:230)
    2011/10/12 16:06:33 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.formula.Formula.processRow(Formula.java:97)
    2011/10/12 16:06:33 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    2011/10/12 16:06:33 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Unknown Source)
    2011/10/12 16:06:33 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: java.lang.ClassCastException: java.lang.Boolean cannot be cast to java.util.Date
    2011/10/12 16:06:33 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.formula.Formula.calcFields(Formula.java:209)
    2011/10/12 16:06:33 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 3 more
    2011/10/12 16:06:33 - Formula.0 - Finished processing (I=0, O=0, R=3, W=2, U=0, E=1)
    2011/10/12 16:06:33 - SetDatePutEverythingToExcel - SetDatePutEverythingToExcel
    2011/10/12 16:06:33 - SetDatePutEverythingToExcel - SetDatePutEverythingToExcel
    2011/10/12 16:06:33 - Add constants.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=0)
    2011/10/12 16:06:33 - Spoon - The transformation has finished!!
    2011/10/12 16:06:33 - SetDatePutEverythingToExcel - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2011/10/12 16:06:33 - SetDatePutEverythingToExcel - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Is there any chance that you are getting a 0 date or a True or something else odd in your input?

    If you include a sample Excel in the RAR, then we can test most of it. Serialize to file is a good alternative to Table Output.

  3. #3
    Join Date
    Sep 2011
    Posts
    171

    Default

    I just checked, there is excel file attached.

    Actually, I have empty cell in my input, but why would it matter?

    Regards,
    Alex

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Can't tell you why, but it doesn't like the NULL in your last date.

    ** I would consider it to be a bug (File JIRA) in that it seems to be treating the contents as the arguments...

    It then returns True or False to be converted to Date.
    Last edited by gutlez; 10-12-2011 at 06:54 PM.

  5. #5
    Join Date
    Sep 2011
    Posts
    171

    Default

    Yeap! Make sense! I will replace particular field with static value.

    Thank you, gutlez!

  6. #6
    Join Date
    Sep 2011
    Posts
    171

    Default

    Okay, I replaced field with constant date in the formula component, but now PDI thinks that it should be a number? I attached transformation with excel file.

    I also tried putting date in quotation marks, but in this case PDI thinks it is string.



    2011/10/13 14:14:25 - Spoon - Transformation opened.
    2011/10/13 14:14:25 - Spoon - Launching transformation [SetDatePutEverythingToExcel]...
    2011/10/13 14:14:25 - Spoon - Started the transformation execution.
    2011/10/13 14:14:25 - SetDatePutEverythingToExcel - Dispatching started for transformation [SetDatePutEverythingToExcel]
    2011/10/13 14:14:25 - Transformation metadata - Natural sort of steps executed in 1 ms (5 time previous steps calculated)
    2011/10/13 14:14:25 - Table output.0 - Connected to database [iris_staging] (commit=1000)
    2011/10/13 14:14:26 - MainRows Excel Input.0 - Finished processing (I=3, O=0, R=0, W=3, U=0, E=0)
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Unexpected error
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleValueException:
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleValueException:
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Please specify a Number type for field [DummyField] as a result of formula [If([InstrumentType]<>"Cash equivalent";2011/10/13; 2011/10/13)]
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Please specify a Number type for field [DummyField] as a result of formula [If([InstrumentType]<>"Cash equivalent";2011/10/13; 2011/10/13)]
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.formula.Formula.calcFields(Formula.java:230)
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.formula.Formula.processRow(Formula.java:97)
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Unknown Source)
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleValueException:
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Please specify a Number type for field [DummyField] as a result of formula [If([InstrumentType]<>"Cash equivalent";2011/10/13; 2011/10/13)]
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.formula.Formula.calcFields(Formula.java:161)
    2011/10/13 14:14:26 - Formula.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 3 more
    2011/10/13 14:14:26 - Formula.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2011/10/13 14:14:26 - SetDatePutEverythingToExcel - SetDatePutEverythingToExcel
    2011/10/13 14:14:26 - SetDatePutEverythingToExcel - SetDatePutEverythingToExcel
    2011/10/13 14:14:26 - Spoon - The transformation has finished!!
    2011/10/13 14:14:26 - SetDatePutEverythingToExcel - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2011/10/13 14:14:26 - SetDatePutEverythingToExcel - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Instead, try putting a "If field value is null" step between your Excel Input and your Formula, then use your formula the way it was before.

  8. #8
    Join Date
    Sep 2011
    Posts
    171

    Default

    Thank you, Gutlez! It worked.

    Still, I am wondering why formula component is so particular about the format, or maybe I do not understand something?
    What if I need to do some kind of transformation like: If fieldA = "Something", Then replace FieldB with"Something else" Else replace FieldC with "something else"

    Regards,,
    Alex

  9. #9
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    As I said, I would consider it a bug, and report it on JIRA.

    The formula step can do what you're talking about, it just currently wants values in the fields.
    You could use a Filter Rows step and use the Set Value from Field step.

    Many ways to do things....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.