Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Generate Multiple rows based on date

  1. #1

    Default Generate Multiple rows based on date

    How can we split a rows to multiple rows based on date field. For eg. consider the following record.

    ID FROM_DATE TO_DATE
    1 01-Jan-2017 31-May-2017
    2 01-Jan-2017 31-Sep-2017

    I want to convert the above to below.

    ID FROM_DATE TO_DATE
    1 01-Jan-2017 31-Jan-2017
    1 01-Feb-2017 28-Feb-2017
    1 01-Mar-2017 31-Mar-2017
    1 01-Apr-2017 30-Apr-2017
    1 01-May-2017 31-May-2017
    1 01-Jun-2017 30-Jun-2017
    2 01-Jan-2017 31-Jan-2017

    and so on..

    Has anyone implemented this and any tips on how to implement this quickly. Thanks.

  2. #2
    Join Date
    Sep 2011
    Posts
    152

    Default

    hint is : you can use clone rows and then group on month to get the last value, of course you can use add sequence to get your id repeating.

  3. #3

    Default

    Thanks for the hint. We were able to implement the same. Trick is to get the months_between in Oracle and use that in clone rows to get multiple rows. Then used clone number to get the begin and end date. Was able to implement the same after few hiccups.

  4. #4
    Join Date
    Sep 2011
    Posts
    152

    Default

    Good to know ... problem resolved.

Tags for this Thread

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.