Recently I had a use case in which I needed to create a date table/ calendar table and to join several fields to this newly created date table. I would like to share how easy this was by using Alteryx.
Your tool of choice will be the Generate Rows” tool. In my case I want to create a new table.
I had another table that contained a field “Start date” and I wanted my calendar table to start with the first start date of this table and to end with todays date. So I used my table containing the “Start date” field. By sorting the “Start date” ascending and using the summarize tool to get the first Start date I had the input I needed for the “Generate rows” tool. In this case my input was a single value.
The image below shows the configurations done within the tool. I created a new field “Date” and set the initialization expression (the date I want to start with) to “First_Start date” (output from Summarize Tool).
The Loop Expression determines the increment. In my case I want to show every date within the period, so my increment is 1 day which is expressed by using the DateTimeAdd Funtion. The loop will be executed as long as the Condition Expression is true. In my case as long as the new created date field is <= today.
Any questions? Feel free to ask!