Populate records for missing dates

[aokalabak]

Dear all,

I have an excel file with departure data, like 

DepartFrom	DepartDate	TotalCount
LA	01.03.2018	2
LA	04.03.2018	4
LA	07.03.2018	3

for missing dates, I want to populate rows with TotalCount=0, final data will be like 

DepartFrom	DepartDate	TotalCount
LA	01.03.2018	2
LA	02.03.2018	0
LA	03.03.2018	0
LA	04.03.2018	4
LA	05.03.2018	0
LA	06.03.2018	0
LA	07.03.2018	3

I tried join and union tools, prepared another excel sheet containing missing dates and merge two files but somehow I couldn't find a way. 

Is there a way to populate values for missing dates?

Thanks in advance

Ahmet

[LordNeilLord · Accepted solution]

Hey @aokalabak

Here's how to get the answer:

1. Using summarize tool, group by departfrom and return min & max departdate
2. Use generate Rows tool to fill all the dates between min & max
3. Join this back to your original file using date as the join criteria
4. Union the left (or right depending on how you did the join) and join output to get a complete list
5. Use the formula tool (or data cleanse) to turn the null Total Count into zero

Let me know if any of that doesn't make sense

Neil

[aokalabak]

Thanks @LordNeilLord, this worked :)

Kind regards,

Ahmet

[BenMoss]

Depending on the complexity of your datasource this is (given the sample you shared) possible with just one tool, the 'TS Filler' tool, which is part of the 'Time Series' toolkit.

The tool is designed to fill in missing dates.

Example attached.

Ben

[kpombo]

Can you post a workflow example for this? I am having trouble getting the generate rows tool to work.