KNOWLEDGE BASE

## How to Fill Gaps between Two Data and Time Fields

Published: 08 Dec 2023

### Question

How can we fill the gaps between two Data and Time fields in Tableau Prep Builder?

Assume that we have the following data.

If we would like to fill the gaps between the Start and End data at a time interval of 30-minutes, how can this be achieved?

### Environment

• Tableau Prep Builder

Follow along in the sample packaged flow found in the Attachments section to fill gaps between two Data and Time fields.

2. Create a calculated field and name it Difference in days.
`DATEDIFF('day',[Start],[End])`
3. Create a calculated field and name it Start (minute).
`DATEPART('hour',[Start]) * 60 + DATEPART('minute',[Start])`
4. Create a calculated field and name it End (minute).
```[Difference in days] * 1440
+ DATEPART('hour',[End]) * 60
+ DATEPART('minute',[End])```
5. Add Pivot step and select End and Start fields from the left pane, and drag them to the Pivoted Fields pane.
6. Add New Rows step and configure it as follows.

8. Create a calculated field and name it Filter.
```[(minute)] <=
[Difference in days] * 1440
+ DATEPART('hour',[End]) * 60
+ DATEPART('minute',[End])```
9. Add a filter from the following location and select True only.

10. Create a calculated field and name it Difference in minutes.
```IF [(minute)] < 1440 THEN 0
ELSE [(minute)] % 1440 + 30
End```
11. Create a calculated field and name it New Rows.
```IF [(minute)] < 1440 THEN
STR(DATE([Start]))
+ " "
+ RIGHT("00" + STR(FLOOR([(minute)] / 60)),2)
+ ":"
+ LEFT(STR([(minute)] % 60) + "00",2)
+ ":"
+ "00"
ELSE