Splitting rows into multiple records based on date range

I have a list of customers and their insurance policies. The dataset has the columns: Customer number, Policy number, start date, end date.

A customer policy record may span multiple years, depending on how many times the customers has renewed the policy.

I would like to show a separate record for each annual renewal of the policy. For example, if a record started Jan 2014 and ended Dec 2017, I want to show 4 records with the amended start/end dates.

Any help much appreciated.

Thanks

Datasets

Accepted answers

neilgallen

@eahmed008 Pretty simple version of this, which doesn't take into account a few things I can think of, but this will get you started in the right direction.

Generate Rows.yxmd

All comments

Storm

Super succinct response without much detail: Look into a combination of the "Generate rows" tool and the multi-row tool. This is your friend for use cases like this, where you want discrete records within some range where you didn't start with discrete records.

neilgallen

@eahmed008 Pretty simple version of this, which doesn't take into account a few things I can think of, but this will get you started in the right direction.

Generate Rows.yxmd

eahmed008

Thanks both for your help - that did the trick!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703