Five time-saving Alteryx tips, part 4: Filling the date gaps with the TS Filler tool

by Anna Prosvetova

Sometimes during the data preparation process you might need to check if there are any missing dates in the dataset that need to be created. Or you might need to create a list of dates for a particular period that you wish to join to your original dataset for further manipulation. Alteryx’ s tool palette has a great solution called TS Filler (part of the Time Series tools category). The TS Filler tool checks if there are any missing dates in the dataset and fills in the missing values. It is usually used as a preparation step for time series analysis.

For example, you have a time series that is supposed to contain values for every day, but some values are missing. As an example, I created this sample dataset using the Text Input tool.

To check which values are missing and add them to the dataset, let’s bring the TS Filler tool to the workflow and configure it as follows:

  • Select Date or DateTime column: it is the Date field in our case.
  • Interval: in this example it’s ‘Day’. Other available options are Minute, Hour, Month, Week, and Year.
  • Increment: this is the increment by which each unique time series period should be separated. In our example it should be 1, i.e. one day, as we are looking at daily data. You can set the increment to any integer from 1 to 100.

For the TS Filler tool to work, make sure that the field you want to interpolate is of type Date or DateTime.

The output of the tool will contain all rows in the dataset plus any new generated rows to fill the gaps in the time series. The tool will also append two new fields to the original dataset:

  • OriginalDateTime: these are your original date and time values
  • FlagGeneratedRow: this is a flag to indicate whether the data is generated by the tool (‘True’) or comes from the original dataset (‘False’). 
Output after the TS Filler tool is applied

Let me know if you have any questions and make sure to check out the other posts in my ‘Five time-saving Alteryx tips’ series:

Cover image credit: Photo by Adam Tinworth on Unsplash