Filling gaps in a timeline (days, weeks, week number, etc.)

Ask the Community
1

I was wondering how to easily fill in gaps on a timeline.

I am basically looking to get 1 row per week and I have a input dataset which misses weeks. The input dataset has a “random” date in the week, a week number (of the year) and a year. But lots of gaps that keep changing and the dataset keeps updating. So not keen to have a massive lookup table over decades of weeks.

Goal is to get an output with a row per week and in each year starting with the earliest in the input and ending with the latest in the output. I then keep the other properties from the input as null if it is a missing week.

2

When you say the input dataset keeps changing, do past weeks/days get filled in or are filtered out? Or only future dates/dates past a certain time

3

Don’t know. New rows may be added in terms of extending the timeline or rows can be added where we previously had blanks.

4

The solution that comes to mind is to extract the earliest and latest dates from your dataset and create a new table that contains all weeks between the earliest and latest dates and then join this with your original dataset to fill in the missing weeks. (Technically this is probably the look up solution you mentioned that you didn’t want to do but I don’t know if you can avoid doing a join)

You could also try using the Date sequence board (and specify the step unit as weeks) or sequence board to generate an array of dates and explode that to try and get what you’re looking for

1 Like