---
schema_version: "1"
id: 23ac0de03314536b4114933998b0569529be011333a0d4493adce153b4570fa5
title: fill
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/analytics/bi_dev_guide_saql/guides/reference/bi-saql-statement-fill.md
canonical_url: https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-statement-fill.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 5eb048f0baff0018cc7ef7338e0602de902226c5cbfaeec3218d3c3d439ea63b
products:
  - Analytics SAQL Developer Guide
  - analytics
---

# fill

Use `fill()` to fill in any gaps in date fields. You most often use `fill()` before using the `timeseries` statement. By specifying the date fields to check, `fill()` creates a row that contains the missing month, day, week, quarter, or year and includes a null value. To include values outside the bounds of your data’s date range, specify a start date and end date to override existing limits. The function returns the missing date rows with null values.

## Syntax

```nolang
results = fill resultSet by (dateCols=(dateField1, dateField2, "<date format>"), startDate=startDate, endDate=endDate, [partition])
```

<sfdocstbl><table>

<col>
</col>
<col>
</col>
<thead>
  <tr>
    <th>
      Name
    </th>
    <th>
      Description
    </th>
  </tr>
</thead>
<tbody>
  <tr>
    <td>
      <code>resultSet</code>
    </td>
    <td>
      Required. The results of a query that serve as input to the
      <code>fill()</code>
      function. This
      <code>resultSet</code>
      must have non-null input, or the
      <a href="https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi_saql_statement_timeseries.md" title="Uses existing data to predict future data points. The timeseries statement must follow a projection statement in your query. Perform any filtering pre-projection or after the timeseries statement."><code>timeseries()</code> </a>
      statement fails when run.
    </td>
  </tr>
  <tr>
    <td>
      <code>dateCols</code>
    </td>
    <td>
      Required.
      <p>
        <code>&lt;span&gt;date_fields&lt;/span&gt;</code>
        —The date fields in which to check for gaps.
      </p>
      <p>
        The
        <code>&lt;span&gt;date format&lt;/span&gt;</code>
        string accepts these values.
      </p>
      <ul>
        <li>
          <p>
            <code>'&lt;span&gt;yearField&lt;/span&gt;'</code>
            , ‘
            <code>'&lt;span&gt;monthField&lt;/span&gt;'</code>
            ,
            <code>'Y-M'</code>
          </p>
        </li>
        <li>
          <p>
            <code>'&lt;span&gt;yearField&lt;/span&gt;'</code>
            ,
            <code>'&lt;span&gt;quarterField&lt;/span&gt;'</code>
            ,
            <code>'Y-Q'</code>
          </p>
        </li>
        <li>
          <p>
            <code>'&lt;span&gt;yearField&lt;/span&gt;'</code>
            ,
            <code>'Y'</code>
          </p>
        </li>
        <li>
          <p>
            <code>'&lt;span&gt;yearField&lt;/span&gt;'</code>
            ,
            <code>'&lt;span&gt;weekField&lt;/span&gt;'</code>
            ,
            <code>'Y-W'</code>
          </p>
        </li>
        <li>
          <p>
            <code>'&lt;span&gt;yearField&lt;/span&gt;'</code>
            ,
            <code>'&lt;span&gt;monthField&lt;/span&gt;'</code>
            ,
            <code>'&lt;span&gt;dayField&lt;/span&gt;'</code>
            ,
            <code>'Y-M-D'</code>
          </p>
        </li>
      </ul>
      <p>
        <code>startDate</code>
        —The starting date value beyond the scope of your data's date range.
      </p>
      <p>
        <code>endDate</code>
        —The ending date value beyond the scope of your data's date range.
      </p>
      <ul>
        <li>
          <p>
            You can use
            <code>startDate</code>
            and
            <code>endDate</code>
            together or one and not the other.
          </p>
        </li>
        <li>
          <p>
            If you leave out
            <code>startDate</code>
            , then the start date is the earliest date in your dataset.
          </p>
        </li>
        <li>
          <p>
            If you leave out
            <code>endDate</code>
            , then the end date is the latest date in your dataset.
          </p>
        </li>
        <li>
          <p>
            If
            <code>startDate</code>
            and
            <code>endDate</code>
            are within the bounds of your dataset,
            <code>fill()</code>
            ignores them.
          </p>
        </li>
      </ul>
    </td>
  </tr>
  <tr>
    <td>
      <code>partition</code>
    </td>
    <td>
      Optional. A named parameter used to split query results into smaller parts. The
      <code>fill()</code>
      function resets when the named parameter value changes. After each group of rows is completed for a given partition,
      <code>fill()</code>
      runs on the next partition.
    </td>
  </tr>
</tbody>
</table></sfdocstbl>

## Example

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

```nolang
q = load "TouristsData";
q = foreach q generate date_Year, date_Quarter, tourists;
q = fill q by (dateCols=(date_Year, date_Quarter, "Y-Q"));
q = limit q 15;
```

The query first 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.

These are the results from `q = load "TouristsData"; q = foreach q generate date_Year, date_Quarter, tourists;`.

| year | quarter | tourists |
| ---- | ------- | -------- |
| 2001 | 1       | 4127     |
| 2002 | 1       | 4173     |
| 2003 | 1       | 4621     |

`fill()` specifies in the `date_cols` array to group the input data by the quarter and year. 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.

| year | quarter | tourists |
| ---- | ------- | -------- |
| 2001 | 1       | 4127     |
| 2001 | 2       | -        |
| 2001 | 3       | -        |
| 2001 | 4       | -        |
| 2002 | 1       | 4173     |
| 2002 | 2       | -        |
| 2002 | 3       | -        |
| 2002 | 4       | -        |
| 2003 | 1       | 4621     |
| 2003 | 2       | -        |
| 2003 | 3       | -        |
| 2003 | 4       | -        |

## Example with Extended Date Range

This query returns null values for tourists where `date_Month` and `date_Year` come before or after the date values in the dataset or there are gaps within the data provided.

```nolang
q = load "TouristsData";
q = foreach q generate date_Year, date_Month, tourists;
q = fill q by (dateCols=(date_Year, date_Month, "Y-M"), startDate="2000-10", endDate="2001-07");
q = limit q 10;
```

| date_Month | date_Year | tourists |
| ---------- | --------- | -------- |
| 10         | 2000      | -        |
| 11         | 2000      | -        |
| 12         | 2000      | -        |
| 01         | 2001      | 41,735   |
| 02         | 2001      | -        |
| 03         | 2001      | -        |
| 04         | 2001      | 26,665   |
| 05         | 2001      | -        |
| 06         | 2001      | -        |
| 07         | 2001      | -        |