TS Filler Tool

The Time Series Filler tool takes a data stream of time series data and fills in any gaps in the series.

This tool is used primarily as a preparation step for using downstream time series-related tools and macros. Some time series tools produce unexpected results or errors if the data stream contains gaps in the time series, e.g. you have a series of data that is supposed to contain measurements every 5 minutes, but you don’t actually have measurements covering every 5 minutes. Use this macro whenever you suspect your data stream may have gaps in it.

This tool uses the R tool. Go to Options > Download Predictive Tools and sign in to the Alteryx Downloads and Licenses portal to install R and the packages used by the R Tool. See Download and Use Predictive Tools.

Connect an input

The TS Filler tool requires a Designer data stream that has a column containing Date or DateTime values.

Configure the tool

Use the Configuration tab to set the controls for how the time series is filled.

  • Select Date or DateTime column: Choose the date or datetime column indicating when the data row was collected.
  • Interval: Select the interval by which the time series is measured. Options include Minute, Hour, Day, Week, Month, and Year.
  • Increment: Select the increment by which each unique time series period should be separated. You can set the increment to any integer from 1 to 100.
To generate a series covering “every 3 weeks” you would set Interval to Week and Increment to 3.

View the output

Every row entering the macro appears in the output. In addition, the macro evaluates if any periods in the time series are missing, and if so, generates a row to fill the gap.

The tool appends two data columns to the incoming data stream:

  • OriginalDateTime: A DateTime column containing the original, unaltered DateTime value
  • FlagGeneratedRow: A Boolean flag column, with “True” indicating that the data row was generated by the macro to fill a time series gap; “False” indicating it’s an source data row.

For source data rows passing through the macro:

  • The FlagGeneratedRow column will read “False” to indicate that it is a source data row; not a row generated by the macro.
  • The [yourDateTimeColumn] column will be “rounded” to the appropriate period. See “Rounding” section below.
  • All other source columns will pass through unaltered.

For the rows that were generated to fill gaps:

  1. The FlagGeneratedRow column will read “True” to indicate that it is a row generated by the macro, not a source data row.
  2. The [yourDateTimeColumn] column will have a populated date or datetime value generated by the macro which “fills” the gap identified in the series.
  3. All other columns will be null.