How to populate missing events

[Anonymous]

Hi community,

I've got a challenging task to do and need some help/ideas please!

The data contains a sequence of events within 12 weeks from the date of the first event. This means I have a StartDate for every single entity (customer) in the dataset and I move forward 84 days to get the EndDate (week 12). I then divide the period into 12 weeks.

Note that not all the weeks contain an event which means only when an event happens there is a record for that.

I have successfully populated the weeks column.Ideally, I'll have to populate the events based on these criteria:

1- If there is no event in week 1 the status should be 'Still Selected'

2- If no event happens in the subsequent weeks, the status from previous weeks will be carried over to the next weeks until the next event happens.

3- If a "Failed" event happens and the immediate event before that is a "Booked" that occurred for the second times (the second Booked), then the next event should be "Rebooked" otherwise it remains "Booked".

Below is what I'm trying to achieve:

Hopefully it all makes sense, any thoughts and ideas are welcome

Thanks !