In this blog I will show you how to add missing dates to a date series table using the Generate Rows tool in Alteryx. Our final flow will look like this:
Here we have some sales data for our personal online retail business summarized to a daily level. Unfortunately on days where we don't make any sales there is no record at all. We want to fix this so that we have a record for every day regardless of whether or not there are sales.
In the above data set the Date is already in a date format that Alteryx understands (yyyy-MM-dd), if yours is not you may need to first convert it using the DateTime tool.
When your date field is ready, the first step is to use a Summarize tool to find our Minimum and Maximum dates within our data set.
Now drag in the Generate Rows tool and in the first section set the tool up with the following settings. Create a new field called Date and ensure the data type is also set to Date. Set your initialization expression to the [Min_Date] field. This instructs the tool where to begin generating rows from.
Set your condition expression to [Date]<=[Max_Date], this tells the tool to only produce new rows until it reaches the Maximum Date.
In the Loop Expression use the following formula:
datetimeadd([Date],1,'days')
This instructs the tool add 1 day to the date with every row generated.
After this you should have a table with a row for every date in your desired final data table, as below:
Use a select tool to remove the Min_Date and Max_Date columns and leave just your newly generated date rows before using a Join tool to join the output back to the original data set using the two date fields in a join condition. In the join tool unheck one of the Date columns so that you don't have two duplicate date columns in the Joined data.
In the Join section of the output data you will see a table matching your original data, but crucially in the L or R outputs (depending which way round you joined your inputs) you shoul have a table consisting of only the missing dates that you require.
Using a Formula tool on the missing dates output, create a new column called Sales and set the value to 0. This will allow us to easily union these dates onto the original dates in the initial data set.
Use a Union tool followed by a Sort tool to sort the date column by ascending:
You should now have your final output where there is a row for each date and any date without sales has a sales value of 0.
I hope this was useful!