How to Fill Gaps between Two Data and Time Fields

Published: 08 Dec 2023
Last Modified Date: 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.
User-added image

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

Answer

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

1. Add Clean step.
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.
　 User-added image

7. Add Clean step.
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.
　 User-added image

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
	STR(DATEADD('minute',[Difference in minutes],[Start]))
End

12. Right-click the unnecessary fields in the Profile pane and select Remove.