Generating Dates in Alteryx

Question

Is there a way that I can generate dates between a Start and an End Date by using a RecordID as the Initialization Expression? The reason is because some of my data has duplicates whether that be the date or a different part of the data and I want to still generate dates to fill in between them whether part of it is duplicate or not. If I use the starting date for each record than it leaves some of the records out after generating because of duplicate information. But if I could use the recordID which is unique to each one than it would include all of the records.

Answer 1

First, isolate your desired start and end dates, for instance use the Summarize Tool to generate minDate as min([yourDateField]) and maxDate as max([yourDateField]).

Then send those into a Generate Rows tool. In that tool, specify that you're creating a new variable, let's call it [newDate]. For Initialization Expression, simply specify [minDate], for Condition Expression, specify [newDate. <= [maxDate], and for Loop Expression, specify DateTimeAdd([newDate],1,'days') ... the output from that tool will be all the days (as rows) from minDate to maxDate.

Then Join that back to your main data on the newDate = yourDateField ... and finally, Union the outputs of the Join tool together to end up with empty rows with the new dates intermixed with populated rows of your original data.