cancel
Showing results for 
Search instead for 
Did you mean: 

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

vaidyarm
Contributor

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

1 ACCEPTED SOLUTION

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)

View solution in original post

9 REPLIES 9

SpiroTaleski
Valued Contributor

@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

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

tlikarish
Employee
Employee

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

image

row-split-example_2021_09_29.slp (9.1 KB)

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