Last week I came across an issue where I had a start date and end date per customer ID, but was joining to data that had daily inputs. Little did I realise Andy Pick had used a Custom SQL to create this but I managed to do this through using the generate rows tool in Alteryx.
In this blog post I will show you how to use the start date and end date to create new rows with daily dates.
So let’s input the data into Alteryx. N.B This data is made up. As you can see we have a User ID that has a start date and end date.
The next tool is the ‘Generate Rows’ tool
Now we need to configure this tool. First make sure the create new field is selected, rename the field to whatever you like, then make sure the type is either date or date time depending on your data set.
Next is to set the ‘Initialization Expression’ which is simply the starting date.
The next section is the ‘Condition Expression’. This is essentially where you want the date range to go up to. So for the first example it would be up until the end date. The first date is the newly created field ‘DailyDate’
Now the last equation needed is the ‘Loop Expression (Usually Increment)’. This is essentially telling Alteryx how many new rows do you want to create based on the expressions you’ve used above. This formula is saying add 1 day to each date. This will then stop when the daily date reaches the end date which is specified in the condition expression.
If we add a browse on the end this now shows we have 314 rows, and for the User ID 1 we have 30 records going from the 01/02/2016 to 01/03/2016.
If you don’t have an end date, you can always use the ‘datetimenow()’ formula in the condition expression section. Just be aware if you use the datetimenow and you have dates ranging back to a couple of years there will be a lot of records. Only use this if you have a multiple dates within a joining dataset, then you can just join on User ID and the DailyDate which will then remove all other unnecessary dates created.
I hope you find this blog post helpful. Feedback is welcomed and let me know if it works or you have any troubles recreating this.