This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 07-21-201604:11 PM - edited on 04-22-201910:39 AM by SydneyF
Let's say you have a dataset with products that aren't sold very often (not every month) but you would like to create data points for every month and fill in quantity and amount as zero for reporting purposes.
This is the perfect time to use the Generate Rows tool. It will allow you to take the earliest month on the dataset, increment that by one month (generating a new row each time) until it has reached the latest month or the current month you are in.
To do so, first use a Summarize Tool to identify both the min (earliest) and max (most recent) dates on your data.
In this example, we created a field called [CurrentMonth] to be used as the max date, but you could certainly use a date provided on your data set.
Then, set up the Generate Rows tool as follows:
We are creating a new field called [Date] that will hold all of the date values between the earliest date on the data set and the current month.
The Initialization Expression [Min_Date] is the starting value that will be used for the first row. In our case, it is the earliest date on the data set as identified by the summarize tool.
The Loop Expression is what is being used to generate new rows. In our case, it increments [Date] by one month until the Condition Expression is met. As long as the Condition Expression returns true, another row will be created. Once the Condition Expression returns false, no more rows will be created.
Once these rows are created, you will have to join them back to your dataset and fill in any missing columns appropriately. See the attached workflow for an example of how to do that.