Forum Discussion

vaidyarm's avatar
vaidyarm
Contributor
5 years ago
Solved

How to split date into multiple rows from one row based on specific condition

Hi

I need to form multiple records from one row based on date criteria, and the criteria are that when start date and end date have a difference of more than 1 year, it should have multiple rows for each year date as mentioned below:

Input :

ID	StartDate	EndDate
1	2016-01-12	2019-11-30

Output :

ID	StartDate	EndDate
1	2016-01-12	2016-12-31
1	2017-01-01	2017-12-31
1	2018-01-01	2018-12-31
1	2019-01-01	2019-11-30

9 Replies

    • vaidyarm's avatar
      vaidyarm
      Contributor
      5 years ago
      Copy Link

      Have no words to thank you for this, I always thought to do such things via python script, but the core snaplogic solution you gave is very well thought !!

      Thanks a lot !!

    • vaidyarm's avatar
      vaidyarm
      Contributor
      5 years ago
      Copy Link

      Thanks @tlikarish ,

      This one looks sleek but the problem is that I can have more than the “ID” column in the source, and since you have hardcoded the fields, it would be difficult to incorporate that.
      but this one will be a good one when we have a fixed list of fields coming from the source

      Thanks

    • mohit_jain's avatar
      mohit_jain
      New Contributor III
      3 years ago
      Copy Link

      Hi  tlikarish 
      I saw your pipeline, its perfect but I have one more requirement.
      If I want to spit the date by quarterly can you please let me know where we need to change the pipeline for it.
      For examp:
      Input:

      We just need the start_date and end_date by quarterly. 

       

      THANKS IN ADVANCE.

       

  • j_angelevski's avatar
    j_angelevski
    Contributor III
    5 years ago
    Copy Link

    Hi @vaidyarm,

    You can also do this with a single expression in a Mapper. It is a little bit complex expression but I think it does the job.

    Date.parse($EndDate).getFullYear() - Date.parse($StartDate).getFullYear() > 0 ? sl.range(0, Date.parse($EndDate).getFullYear() - Date.parse($StartDate).getFullYear() + 1).map((val, i, arr) => $.extend({"StartDate": val == 0 ? Date.parse($StartDate).toLocaleDateTimeString({"format": "yyyy-MM-dd"}) : Date.parse($StartDate).withDayOfYear(1).plusYears(val).toLocaleDateTimeString({"format": "yyyy-MM-dd"}), "EndDate": val == arr.length - 1 ? Date.parse($EndDate).toLocaleDateTimeString({"format": "yyyy-MM-dd"}) : Date.parse($StartDate).withMonthOfYear(12).withDayOfMonth(31).plusYears(val).toLocaleDateTimeString({"format": "yyyy-MM-dd"})})) : sl.ensureArray($)

    This will work regardless of how many fields the input data has as long as there is “StartDate” and “EndDate” in the input data.

    Generat_Dates_2021_09_29.slp (5.0 KB)

  • vaidyarm's avatar
    vaidyarm
    Contributor
    5 years ago
    Copy Link

    All of the above solutions worked, I was wondering if we could take this one step forwards with date segregation as below
    (earlier it was as per end of the year, now its one year cycle, and also the diff between the start and end date should not be greater than 365/366 days considering leap years if possible )

    Input :

    ID	StartDate	EndDate
1	2016-05-12	2019-11-30

    Output :

    ID StartDate EndDate
1 2016-05-12 2016-11-30
1 2016-11-31 2017-11-30
1 2017-11-31 2018-11-30
1 2018-11-31 2019-11-30

    this is how the contract management system would also be implemented.

    • vaidyarm's avatar
      vaidyarm
      Contributor
      5 years ago
      Copy Link

      I just tried to build on top of your logic to create the above scenario, was able to do it, refer to the attached pipeline, although design-wise it may not seem that cleaner and concise but it serves the purpose and handles the leap year exception as well
      Input:

      Output:

      It was helpful for contract management problems we have come across so far.

      Contract_Date_splitter_2021_10_11.slp (23.2 KB)