Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Time parameter

  1. #1
    Join Date
    Apr 2014
    Posts
    7

    Default Time parameter

    Hi,

    I need to fill my table Time, with columns (idT, Day, Month, Year, Date). The idea is to insert parameter for a particular date, and then that day will be inserted into my table. Any help how to do that? I don't know how to handle with parameters in Pentaho Spoon, I tried several ways, but I always generate the whole table with some results, for example, from 01.01.2012 to 01.01.2018, and my table has all values for that time period. I need to generate some bugs by day, and my idT needs to be in format 20140805, for example (08.05.2014). Thanks in advance, any help is important.

  2. #2
    Join Date
    Apr 2014
    Posts
    7

    Default

    Just a few more details... I need two parameters, StartDate and EndDate, and then, and when I select those two parameters, I need to insert in my table bugs from that particular day(in table bugs I have column creation_date), it needs to be generated day by day

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

    Default

    You want to extract rows from a database table filtering for a particular time interval?

    You can do that with a Table Input step:

    Code:
    SELECT some, column, names, here
    FROM your_source_table
    WHERE creation_date BETWEEN '${StartDate}' AND '${EndDate}'
    Don't forget to enable variable replacement.
    So long, and thanks for all the fish.

  4. #4
    Join Date
    Apr 2014
    Posts
    7

    Default

    Thanks, I will try with this. But how to generate my id in form I mentioned (20140805)? I suppose it's a 2014*10000+8*100+5, but how to handle that in Pentaho?

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

    Default

    You can specify a format string (SimpleDateFormat) yyyyMMdd for a date field to get what you want, either in your input / output step, or use a Select Values step to change meta data.
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Apr 2014
    Posts
    7

    Default

    Ok, just one more question. Is it possible to create a transformation in which I can insert two parameters, start date, and end date, and to generate those days in my table output (in my table time with columns idT, Day, month, Year, Date)? I mean without any table input (not to look to any other table for dates). Thanks in advance

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

    Default

    The tough part for you isn't the Table Output, I guess, so here's how to create dates within a range.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  8. #8
    Join Date
    Apr 2014
    Posts
    7

    Default

    Where here to insert my End Date? I consulted with my boss and I need to just generate date values from start date to end date and insert it in my database(nothing to import from another table, just to generate). I'm still a student and this is the first time to work with Pentaho and I'm a little confused with all this. For example, start date: 2012/05/01, end date: 2012/05/05, and it needs to write 2012/05/01,2012/05/02,2012/05/03, 2012/05/04, 2012/05/05.

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

    Default

    Recommended reading: Named Parameters

    The one who assigned you to this task should be able to provide the necessary support, given you don't know anything about Kettle - don't you think so?
    So long, and thanks for all the fish.

  10. #10
    Join Date
    Apr 2014
    Posts
    7

    Default

    Forgot to say, many thanks for this

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.