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:
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?
Gelöst! Gehe zu Lösung.
Thanks, @DavidP! That's a thing of beauty. Works perfectly.
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:
IF
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