Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Problem with data in formula expression step

  1. #1
    Join Date
    Feb 2014
    Posts
    10

    Default Problem with data in formula expression step

    Hi,

    I'm a new user of PDI and I have a problem when I try to change the date value to another date value using a formula expression step.

    I used the formula:

    IF(ISBLANK([PHRENIC1]);[PHRENIC1];IF(YEAR([PHRENIC1])=YEAR(TODAY());[PHRENIC1];IF(DAY([PHRENIC1])=1;DATE(YEAR([PHRENIC1]);MONTH([PHRENIC1]);15);[PHRENIC1])))

    to change the day 01 to day 15 on dates stored in field PHRENIC1.

    I used the same expression in the same step to another field and it works, but to field PHRENIC1 appear the preview error:

    Row Preview - ERROR(version 4.4.0-stable, build 17588 from 2012-11-21 ...) : PHRENIC1_2 Date: There was a data type error: the data type of java.lang.String object [2003-11-15] does not correspond to value meta [Date]

    PHRENIC1_2 is the new field where the result of the formula will be stored. The step does not give error, but the field PHRENIC1_2 is empty.

    I checked the Excell and all step before the formula step and the data type of PHRENIC1 is date. I do not know why this does not work. Anybody has some idea? Please, I will really appreciate any help.

    Val_Qc

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    We know that your formula works, so compare the metadata from PHRENIC1 and the other field.
    Use action "Show input fields" from the Formula step menu to do this.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Feb 2014
    Posts
    1

    Default

    I'm having a similar problem. Let us know if you figure something out.

    Thanks,
    Paul

  4. #4
    Join Date
    Feb 2014
    Posts
    10

    Default

    Thank you marabu I will try your suggestion.

  5. #5
    Join Date
    Feb 2014
    Posts
    10

    Default

    Hi,

    I could not test the marabu's suggestion because I do not need the phrenic1 anymore. However, I will test it when I have some free time. Now I have another problem with formula expression involving dates. I wrote the following formula expression:

    if(isblank([AGE]);"Unk";if([AGE]>[Date-Exam];"Negative";"Positive"))

    I think this formula is correct, but in my new field Age2, that keep the result of this formula, just appear "Positive" even when Age > Date-Exam.

    Some date examples:
    Date-Exam Age
    2006/11/07 2007/06/04
    2007/09/03 2007/06/04

    Anybody know why this is happing?
    Thank you in advances for any help.

  6. #6
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Works for me, when date format "yyyy/MM/dd" is applied.
    So long, and thanks for all the fish.

  7. #7
    Join Date
    Feb 2014
    Posts
    10

    Default

    Quote Originally Posted by marabu View Post
    Works for me, when date format "yyyy/MM/dd" is applied.
    Muitíssimo obrigada marabu. Um dos campos estava como string e não como data. Passei a tarde toda de ontem tentando ver o que se passava e não dei por isso. Valeu :-)

  8. #8
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    De nada
    So long, and thanks for all the fish.

Tags for this Thread

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.