FILL

Use the FILL() table function to fill in any gaps in date fields. By specifying the date fields to check, FILL() creates rows that contain the missing month, day, week, quarter, or year and null data. Use it with TIMESERIES() to forecast future results when there are gaps in input data.

FILL() takes the following syntax.

SELECT <PROJECTION_LIST>|* FROM FILL(
    INPUT=>(SELECT STATEMENT),
    DATE_COLS=>ARRAY[<DATE_FIELDS>, 'DATE_COLUMN_TYPE'],
    [PARTITION=>'FIELD_NAME']
)

Example

This example uses FILL() to add missing quarter and year values to tourist data.

SELECT "year", "quarter", tourists FROM FILL(
    INPUT=>(SELECT EXTRACT(YEAR FROM "date") as "year", EXTRACT(QUARTER FROM "date") as "quarter",
tourists FROM "TouristsData"),
    DATE_COLS=>ARRAY['year', 'quarter', 'Y-Q']);

The input SELECT statement returns the year, quarter, and number of tourists for each quarter. Based on the results from the first three years represented in the dataset, the only date data available is for the first quarter.

tip: Another way to project all columns from the INPUT query is by using SELECT * FROM FILL(...).

These are the results from executing only the INPUT SELECT query.

FILL() specifies in the DATE_COLS array to check for gaps in year and quarter fields in the input data. To have a complete dataset of years and quarters, FILL() adds the 2nd, 3rd, and 4th quarters for each year and a null value for the number of tourists.