Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Format date as yyyy-mm-dd using the Java Script Step

  1. #1
    Join Date
    Mar 2014
    Posts
    181

    Default Format date as yyyy-mm-dd using the Java Script Step

    Hi all,

    I have a requirement where I need to pass yesterday's date using the get system info step and assign it to a variable using Java script step and format it as yyyy-mm-dd.
    Then return the formatted date of yyyy-mm-dd and use it to construct a string as

    formated_date = '2014-04-07'

    "/data/tmp/incomplete_payments_"+formated_date+".csv"

    After constructing the string, I need to use it in the Get file from SFTP step to get the file incomplete_payments_2014-04-07.csv.

    Any piece of advise on how to achieve this.

    Thanks,

    Ron

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

    Default

    Try stringing:

    * Get System Data
    * Select Values (Date -> String with format "yyyy-mm-dd" )
    * Calculator (A+B A: "/data/tmp/incomplete_payments_" B: <DateString> ... There are a few gotchas in the middle of this step, but I'll let you stub your toes on them... and do a second A+B A: <output of A+B> B: ".csv")
    * SFTP step

    This is the HIGH LEVEL. If you configure it exactly as I wrote it, you will get errors. Sort them out and you learn :P

  3. #3
    Join Date
    Mar 2014
    Posts
    181

    Default Format date as yyyy-mm-dd using the Java Script Step

    Hi Gutlez,

    I have been trying to reach you.

    I did what you told me.

    I implemeted some transformations that I attached in the http://forums.pentaho.com/showthread...ng-it-in-a-job.

    I have again made some changes since I had some issues with the remote directory, I am able to download the file manually.

    But kettle can cant

    Please see attached files.

    Thanks,

    Ron
    Attached Files Attached Files

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

    Default

    Some feedback:

    You don't need the mapping input and mapping output steps.
    You can do multiple things in a single step. (eg. adding the beginning of the filename and the extension to the stream in the same step)

    You will need to set a variable at the end of the "GetYesterdaysDate.ktr" for the SFTP step to pick up. (eg FILETOGET ) which then gets put in the "Remote Directory" field as ${FILETOGET}

  5. #5
    Join Date
    Mar 2014
    Posts
    181

    Default

    Hi Gutlez,

    I did everything as suggested but I am getting some errors and the problems seems to be around the Set Variables Step before the job executor step.

    I have attached the file and the step.

    Thanks,

    Ron
    Attached Files Attached Files

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

    Default

    You don't need the Generate Rows, nor the Job Executor.

    Get System Info is in the Input folder, so it counts as Input
    Set Variable is a valid end point for a stream

    Try making it:

    Test_SFTP:
    Start -> GetYesterdaysdate -> Get File SFTP

    GetYesterdaysdate:


    • Get System Data
    • Select Values (Date -> String with format "yyyy-mm-dd" )
    • Calculator
    • Set to constant A: "/data/tmp/incomplete_payments_" Remove: Y
    • Set to constant A: ".csv" Remove: Y
    • A+B+C: A: Prefix (field from line 1 of calculator) B:yesterday_date C: suffix (field from line 2 of Calculator)
    • Set Variable
    Last edited by gutlez; 04-09-2014 at 02:02 PM.

  7. #7
    Join Date
    Mar 2014
    Posts
    181

    Default

    lez,

    I have done what you have advised me but I am not able to download the file.

    From the logs, the jobs is doing is expected.

    Please advice.

    Thanks,

    Ron
    Attached Files Attached Files

  8. #8
    Join Date
    Mar 2014
    Posts
    181

    Default

    Quote Originally Posted by gutlez View Post
    Try stringing:

    * Get System Data
    * Select Values (Date -> String with format "yyyy-mm-dd" )
    * Calculator (A+B A: "/data/tmp/incomplete_payments_" B: <DateString> ... There are a few gotchas in the middle of this step, but I'll let you stub your toes on them... and do a second A+B A: <output of A+B> B: ".csv")
    * SFTP step

    This is the HIGH LEVEL. If you configure it exactly as I wrote it, you will get errors. Sort them out and you learn :P
    Gutlez,

    Thank you so much for your help, you made me think harder, I had to do some minor modifications using the exact approach and I was able to download the file.

    I am sharing my code with the rest of the commnunity.

    Thanks,

    Ron
    Last edited by Ron256; 04-09-2014 at 03:37 PM.

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.