Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Date Calculations

  1. #1
    Join Date
    Aug 2008
    Posts
    563

    Default Date Calculations

    I would like to do some date calculations in my transformation. Once I have imported my dataset I want to add a column that stores if a user is active (Y) or not (N). The data type of this column should be Boolean.

    If I do this on import by using SQL the data type might not be really the one that I want (it is quite likely that Kettle identifies it as a string).

    How would I do this:
    IF(datedel > DATE_SUB(CURDATE(), INTERVAL 1 DAY), Y, N) active

    as a seperate step in my transfromation?

    Thanks a lot for you help in advance,
    Diddy

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    That's going to be a javascript snippet you need currently

    Regards,
    Sven

  3. #3
    Join Date
    Aug 2008
    Posts
    563

    Default

    Hi,
    Thanks a lot for your reply. I tried a while now to get this working, but there seems to be a problem with reading in the date (datedel.getDate()).
    My if statement doesn't work at all, I even tried to substract the deletion date from yesterday's date and get 0 or 1970 ... for all my records. I checked both variables and both are in the date format. Do you have any idea what I am doing wrong here?

    Here my code:


    var myDate=new Date();
    myDate.setDate(myDate.getDate()-1);

    // create start of yesterday
    var yesterday = new Date(myDate.getFullYear(),myDate.getMonth(), myDate.getDate(),00,00,00);

    //var check = isDate(datedel.getDate());

    var check = isDate(datedel.getDate());

    var sub = datedel - yesterday;

    if(datedel.getDate() > yesterday){
    var active = 1;
    }
    else
    {
    var active = 0;
    }

    BTW, where can I find a documentation of the built-in Java Functions?

    Thanks a lot,
    Diddy

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    What do you think:

    Code:
    myDate.setDate(myDate.getDate()-1);
    does?

    The function you can use in javascript are in the pdf manual in the docs directory.

    Regards,
    Sven

  5. #5
    Join Date
    Aug 2008
    Posts
    563

    Default Cannot find documentation

    Hi Sven,
    Thanks a lot for your help. I had a look in pdi-open-3.1.0-RC1\docs\English but I cannot find any documentation for this. There is nothing detailled in Spoon_3_0_0_User_Guide.pdf nor in any other document. Can you please point me to the right direction?

    Also, can I just write my own javascript in the 'modified java script value' or do I have to stick to the predifined functions?

    Thanks,
    Diddy

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    Look for the phrase "JavaScript Values" in that pdf file... it will point you to the old javascript step which contains the functions you can use when compatibility is on.

    You can also have a look at the left hand side of the modified javascript in which each "integrated" function is in there per category and you can get example uses if you right click on them.

    You can write own javascript step but remember it's an embedded javascript, not all things work as you would expect them to work

    Regards,
    Sven

  7. #7
    Join Date
    Aug 2008
    Posts
    563

    Default Thanks!

    Hi Sven,
    Thanks a lot. I thought there would be a bit more information than this. I haven't really found a predefined function, but I'll play around a bit more and maybe I get it working.
    Thanks a lot again,
    Diddy

  8. #8
    Join Date
    May 2006
    Posts
    4,882

    Default

    It's the same with Pentaho as with most other software projects... not a lof of developers like writing documentation... so it kind of gets "postponed"/minimalized

    Regards,
    Sven

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.