Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Timestamp fields vs date and ISBLANK()

  1. #1
    Join Date
    Feb 2015
    Posts
    19

    Default Timestamp fields vs date and ISBLANK()

    I am having trouble with dates from a database in a formula
    the simple rule I am trying to implement is
    if(ISBLANK([found_SOFT_CLOSE_DATE]);[PROPOSED_CLOSED_DATE];[found_SOFT_CLOSE_DATE])

    PROPOSED_CLOSED_DATE comes from the source - oracle DB where the column is type 'date'
    From the table input step this has type of 'Timestamp' and is displayed with seconds etc (eg 2017/07/19 00:00:00.000000000)

    the found_SOFT_CLOSE_DATE is from a Database Value lookup step on the target which is Sql Server
    its format is 'date'. Because the transform is yet to run these are all showing in preview as <null> which is fine

    I have returned this column several times from the lookup with different types
    found_SOFT_CLOSE_DATE Date
    test_str_SOFT_CLOSE_DATE String
    test_date_SOFT_CLOSE_DATE Date
    test_ts_SOFT_CLOSE_DATE Timestamp

    The formula seems to fail on two fronts
    if(ISBLANK([test_str_SOFT_CLOSE_DATE]); "is blank"; "already there") always "is blank" (probably correct)
    if(ISBLANK([test_date_SOFT_CLOSE_DATE]); "is blank"; "already there") always <null> how can is isnull be null ??

    Then another formula column
    New field Formula Valuetype result
    new3_SOFT_CLOSE_string = [PROPOSED_CLOSED_DATE_txt] String has the txt version of the date
    new4_SOFT_CLOSE_DATE = [PROPOSED_CLOSED_DATE] Date always <null>
    new5_SOFT_CLOSE_DATE = [PROPOSED_CLOSED_DATE] Timestamp always <null>

    So It feels like ISBLANK() doesn't work with date type field
    and even the simplest formula of just unconditionally using a date field doesn't work.
    could somebody please enlighten me to the error of my ways

    ps I am Using Pentaho datra Integation 7.0

    Thanks
    JC

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

    Default

    Try using ISNA instead of ISBLANK

  3. #3
    Join Date
    Feb 2015
    Posts
    19

    Default

    ISNA gave the same results

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.