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

[vaidyarm]

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

[tlikarish]

You can actually make it dynamic based on the fields by modifying the {Row Per YearDiff} snap like this.

row-split-example_v2_2021_09_29.slp (9.0 KB)

[mohit_jain]

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]

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]

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 · Accepted solution]

vaidyarm:

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

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)