Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Conversion to date

  1. #1

    Default Conversion to date

    Hi,

    I am migrating an ETL from Cognos Data Manager to PDI (version 3.2). The source data is read from a AS400 DB2 table. This table contains a column with a date in a numeric format.

    Examples:
    • 1100714 (representing 2010-07-14, the first 1 indicates it's a 21st century date)
    • 100714 (representing 1910-07-14)
    To convert this to a date we had in Cognos a function as follows:

    if ApicsDatum = 0
    then return toDate(toChar('99991231'),'YYYYMMDD');
    else return toDate(toChar(ApicsDatum + 19000000),'YYYYMMDD');

    When the date on AS400 is 0 this will return date 99991231, otherwise 19000000 is added to the number so it has the correct century date (e.g. 100714 + 19000000 = 19100714). The toDate function turns it in a valid date format.

    In PDI, in a Calculator, I defined a constant with value 19000000, then a 'A + B' calculation to add both values, then a 'Create a copy of field A' with Value type String. This works fine, I get the date in a string value. I thought that I could convert it to a date too by creating a new 'Create a copy of field A' calculation with Value type Date and Conversion mask yyyyMMdd. But this throws an error (e.g. result String : couldn't convert string [ 19890403] to a date using format [yyyyMMdd]).
    What am I doing wrong?

    A second problem is how to handle the 'if' statement in PDI.

    Thanks,
    Patrick

  2. #2
    Join Date
    Oct 2007
    Posts
    107

    Default

    Hello Patrick

    I've attached a sample that convert properly. I was able to reproduce the same error as you so I'm not sure exactly what the problem is but found a way to fix it.

    In my calculator, I had to add an additionnal conversion withe the "RemoveLeadingSpace" as it looks like there was an extra space added at the beginning of the date, afterwards, it convert properly to a date field. You can run the transformation by right clicking on the last dummy step and choosing Preview.

    As for your other question regarding "if", there's various way to do so:
    - Formula
    - Modified Java Script Value
    - User Defined Java Class
    - User Defined Java Expression

    As in example in a javascript, you could use the following:
    MyVariable = A > B ? "Yes" : "No"
    Attached Files Attached Files

  3. #3
    Join Date
    Mar 2010
    Posts
    159

    Default

    Perhaps the leading space in your date string is preventing the conversion. It seems default format for integers is always containing a space prior to the first numeric value... perhaps an intermediate conversion is making that happen.

    Perhaps you can use a format of # for your 19000000 constant?

    Regards,
    Jeremy

  4. #4

    Default More difficult function

    Hi,

    Thanks for the help. It solved my problem. But now I have a more complicate function that I need to migrate :

    if ApicsDatum <> 0
    then if ToInteger(ApicsUur) < 2400
    then
    begin
    $v_Datum_YYYYMMDD := toChar(ApicsDatum + 19000000);
    $v_Uur__HHMM := lpad(ApicsUur,5) ;
    $v_YYYYMMDD_HHMM := concat( $v_Datum_YYYYMMDD, $v_Uur__HHMM) ;
    return toDate( $v_YYYYMMDD_HHMM, 'YYYYMMDD hhmi');
    end
    else
    if ToInteger(ApicsUur) >= 2400 and ToInteger(ApicsUur) <= 2700
    then
    begin
    $v_Datum_YYYYMMDD := toChar(ApicsDatum + 19000000);
    $v_Uur__HHMM := lpad( lpad(toChar(ToNumber(ApicsUur,4,0)-2400),4,'0') ,5);
    $v_YYYYMMDD_HHMM := concat( $v_Datum_YYYYMMDD, $v_Uur__HHMM) ;
    return AddToDate( toDate( $v_YYYYMMDD_HHMM, 'YYYYMMDD hhmi'), 1 );
    end
    else return '0001-01-01 00:00:00';
    else return '0001-01-01 00:00:00'

    It has some nested if statements and works with variables, so I am not sure how to best tackle this. Can anyone put me in the right direction, preferably with an example?


    Many thanks,
    Patrick


  5. #5

    Default

    Goeiemiddag Patrick,

    Like CHamel mentioned, the best way to tackle this is using a Javascript step.
    Have a look at the attached example.

    Best regards,

    Bart
    Attached Files Attached Files

  6. #6

    Default

    Goeiemiddag Bart,

    Thanks for your help !

    Regards,
    Patrick

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.