Do you use Alteryx in a language other than English? If so, we want to hear from you! Please help us improve the international experience of our products by participating in this 5 minute survey.

We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Fill in Missing Dates

jrdnjhnsn2
7 - Meteor

Hi all,

 

I know there's quite a few examples of people filling in missing dates, but I can't figure out how to apply the solutions to my scenario.

The dataset I'm working has quarterly data, but I need to add some additional records to split the data around the issue_date anniversary and fill in the gaps.

How can I create extra rows like I have in my attached desired output? Note the pol_yr_begin and pol_yr_end columns aren't always accurate, but basically what I want is this.

Starting point

issue_datePeriod_EndPeriod_Startpol_yr_beginpol_yr_endtype_of_recordstart_dateend_date
5/1/19856/30/20054/1/20055/1/20054/30/2006 5/1/20056/30/2005
5/1/19856/30/20054/1/20055/1/20054/30/2006 7/1/20059/30/2005
5/1/19859/30/20057/1/20055/1/20054/30/2006 10/1/200512/31/2005
5/1/198512/31/200510/1/20055/1/20054/30/2006 10/1/200512/31/2005
5/1/19853/31/20061/1/20065/1/20054/30/2006 1/1/20063/31/2006
5/1/19856/30/20064/1/20065/1/20054/30/2006 4/1/20066/30/2006
5/1/19859/30/20067/1/20065/1/20064/30/2007 7/1/20069/30/2006
5/1/198512/31/200610/1/20065/1/20064/30/2007 10/1/200612/31/2006
5/1/19853/31/20071/1/20075/1/20064/30/2007 1/1/20073/31/2007
5/1/19856/30/20074/1/20075/1/20064/30/2007 4/1/20074/30/2007
5/1/19859/30/20077/1/20075/1/20074/30/2008 7/1/20079/30/2007
5/1/198512/31/200710/1/20075/1/20074/30/2008 10/1/200712/31/2007
5/1/19853/31/20081/1/20085/1/20074/30/2008 1/1/20083/31/2008
5/1/19856/30/20084/1/20085/1/20074/30/2008 4/1/20084/30/2008
5/1/19858/31/20087/1/20085/1/20084/30/2009 7/1/20089/30/2008

 

Desired Output

issue_datePeriod_EndPeriod_Startpol_yr_beginpol_yr_endtype_of_recordstart_dateend_datecorrect_start_datecorrect_end_date
5/1/19856/30/20054/1/20055/1/20054/30/2006 5/1/20056/30/20055/1/20056/30/2005
5/1/19859/30/20057/1/20055/1/20054/30/2006 7/1/20059/30/20057/1/20059/30/2005
5/1/198512/31/200510/1/20055/1/20054/30/2006 10/1/200512/31/200510/1/200512/31/2005
5/1/19853/31/20061/1/20065/1/20054/30/2006 1/1/20063/31/20061/1/20063/31/2006
5/1/19856/30/20064/1/20065/1/20054/30/2006 4/1/20066/30/20064/1/20064/30/2006
        5/1/20066/30/2006
5/1/19859/30/20067/1/20065/1/20064/30/2007 7/1/20069/30/20067/1/20069/30/2006
5/1/198512/31/200610/1/20065/1/20064/30/2007 10/1/200612/31/200610/1/200612/31/2006
5/1/19853/31/20071/1/20075/1/20064/30/2007 1/1/20073/31/20071/1/20073/31/2007
5/1/19856/30/20074/1/20075/1/20064/30/2007 4/1/20074/30/20074/1/20074/30/2007
        5/1/20076/30/2007
5/1/19859/30/20077/1/20075/1/20074/30/2008 7/1/20079/30/20077/1/20079/30/2007
5/1/198512/31/200710/1/20075/1/20074/30/2008 10/1/200712/31/200710/1/200712/31/2007
5/1/19853/31/20081/1/20085/1/20074/30/2008 1/1/20083/31/20081/1/20083/31/2008
5/1/19856/30/20084/1/20085/1/20074/30/2008 4/1/20084/30/20084/1/20084/30/2008
        5/1/20086/30/2008
5/1/19858/31/20087/1/20085/1/20084/30/2009 7/1/20089/30/20087/1/20089/30/2008
2 REPLIES 2
fmvizcaino
16 - Nebula
16 - Nebula

Hi @jrdnjhnsn2 ,

 

Attached is an example for you.

fmvizcaino_0-1626995863362.png

 

 

Best,

Fernando Vizcaino

jrdnjhnsn2
7 - Meteor

Hi @fmvizcaino,

 

Works perfectly. Thanks so much!

Labels