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

@vaidyarm

I am attaching one possible solution, which is not perfect one, but hope it will cover your requirement.
Community_Child_2021_09_29.slp (3.8 KB)
Community_Parent_2021_09_29.slp (22.0 KB)
Regards,
Spiro Taleski

2 Likes

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 !!

1 Like

Here’s another option that uses a single pipeline that leans on the Expression Language to do most of the work.

row-split-example_2021_09_29.slp (9.1 KB)

1 Like

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

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)

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)

1 Like

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.

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:

image

Output:

image

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

Contract_Date_splitter_2021_10_11.slp (23.2 KB)