Handle the missing dates

[Yoga321]

Hi all,.

I have a data set like shown in image 1 with the date of plans for the year:

 Image 1

But I need the remaining dates as Office  dynamically generated as shown in image 2:

Image 2

I am new to alteryx!!!!!!

Can anyone help in this!!!!!

Regards,

Yoganantha Prakash G P

[neilgallen · Accepted solution]

Hello @Yoga321 and welcome.

The trick here is to generate a dataset that contains all possible dates, and then summarize from there after joining to your existing planned dates.

The attached workflow should get you there, but I'll admit I haven't checked this all that thoroughly against a wide variety of scenarios, just the one you described.

The two generate rows tools 1) creates dates in the planned ranges and 2) creates every date for each person on the list.

The other piece is the tile tool which identifies the sequence of each section.

[jdunkerley79 · Accepted solution]

I did it a slightly different way to @neilgallen

I take each row and see if it is the expected start date after the last. If not then I create an extra row. Finally union back together.

Example attached

[neilgallen]

well done @jdunkerley79. Always expect there to be a simpler method.

[Yoga321]

Hi jdunkerley79,

I got the partial solution as below:

For this employee from date 01-01-2018 to 28-01-2018 is missing and also after 28-06-2018. 29-06-2018 to 31-12-2018 is missing!

Can you help in this!!

[Yoga321]

Hi neilgallen,

Thanks for the reply and got the solution as requested.

[jdunkerley79]

I had only handled back filling.

Have adjusted example to add the mssing final rows