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