Hey all, I've got a bunch of policy data that has issue date and termination date (which may or may not be null). I'm trying to figure out how to split each record based on its "policy anniversary." For example, let's say a policy has an issue date of 10/10/2007 and termination date 12/10/2009. I want to create separate records for each of these date ranges: * From 10/10/2007 issue date though 12/31/2007. * From 1/1/2008 through 10/9/2008. * From 10/10/2008 through 12/31/2008. * From 1/1/2009 through 10/9/2009. * From 10/10/2009 through 12/10/2009 (the termination date). Using DummyID 4 in my sample data as another example, my desired output is as follows: DummyIDPolicy_Start_DatePolicy_End_Date41954-09-201954-12-3141955-01-011955-09-1941955-09-201955-12-3141956-01-011956-09-19 . . . 42019-09-202019-09-24 Does anyone know how this can be done? Community Sample.yxdb

Splitting Records Based on Dates

Hey all,

I've got a bunch of policy data that has issue date and termination date (which may or may not be null). I'm trying to figure out how to split each record based on its "policy anniversary."

For example, let's say a policy has an issue date of 10/10/2007 and termination date 12/10/2009. I want to create separate records for each of these date ranges:

From 10/10/2007 issue date though 12/31/2007.
From 1/1/2008 through 10/9/2008.
From 10/10/2008 through 12/31/2008.
From 1/1/2009 through 10/9/2009.
From 10/10/2009 through 12/10/2009 (the termination date).

Using DummyID 4 in my sample data as another example, my desired output is as follows:

DummyID	Policy_Start_Date	Policy_End_Date
4	1954-09-20	1954-12-31
4	1955-01-01	1955-09-19
4	1955-09-20	1955-12-31
4	1956-01-01	1956-09-19
	.
	.
	.
4	2019-09-20	2019-09-24

Does anyone know how this can be done?

Community Sample.yxdb

Dynamic Processing

Accepted answers

DavidP

Sorry @jrdnjhnsn2 this is getting very embarrassing, I keep messing up the multi-row formula to accommodate all the different angles and in trying to fix one thing, I break another.

As a result, the previous version was wrong too. The attached workflow is the final version where everything works they way it should.

The multi-row formula should be:

IsNull([Row-1:Period Start]) OR
tostring(DateTimeYear([Period Start]))+'-01-01'!=[Period Start] OR
tostring(DateTimeYear([Issue date]))+'-01-01'=[Issue date]

THEN

tostring(DateTimeYear([Period Start]))+'-12-31'

ELSE

DateTimeAdd(DateTimeAdd([Row-1:Period Start],1,'year'),-1,'day')

ENDIF

splitting records based on dates.yxmd

All comments

Sntrada

Hello @jrdnjhnsn2

You should be able to use multiple filter tools to do this.

If you provide a sample of your data, myself or someone else in the community can create a sample workflow using the filter tool, or other tools.

fmvizcaino

Hi @jrdnjhnsn2 ,

Here is a simple example where I'm using the formula to flag the policy and then using a advanced function from the output data tool to separate the data into different excel tabs.