Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Sequence Generation

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

    Default Sequence Generation

    Due to some oddity, I have a database that doesn't like it when I query more than a days worth of data. (ie. "where date_time > <2Days Ago> and date_time < <NOW>" will not return data)

    I have to do a transform on this, and stow the reformatted data in another database.


    What I'm wondering is how to build the series of dates on an automated basis.

    If I do a daily transfrom with Table Input getting the MAX(date_time) from the storage DB, and it fails one day, I need to do the sub-transform twice the next day - one day each time.

    I know there is a plug in which will generate a date sequence, I'm just wondering if there's a better way to do it.
    Last edited by gutlez; 06-18-2009 at 04:17 PM.

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

    Default

    Gah!

    Ignore this thread.


    Solution used:
    Job.

    Transform 1:
    1) Get Max(date_time) from storage db
    2) JavaScript: DateDiff Max(date_time) to now. **Make value an Integer!**
    3) Set Variables Max(date_time) -> LOADFROM DateDiff -> NUMDAYS

    Transform 2:
    1) Generate Rows (Limit ${NUMDAYS} )
    2) Add Sequence (Start at 0, increment 1)
    3) JavaScript StartDate=LOADFROM + Seq Days, EndDate=StartDate+1 day
    4) Copy to Results Stream

    Transform 3:
    Process with DB pull where date_time > ? and date_time <= ?

    Seems to work!

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.