Generate Date values

[evelyn_sherwin]

Hi,
I have a record set with a  date field called date_attended with other information and I would like to generate a row if a row is missing in a month .For example, If the date_attended has values  2013-10-03,2013-10-04,2013-10-06,2013-10-08,2012-10-09
I would like to generate records for 2013-10-05, 2013-10-07. Can anyone advice me how to get this functionality? Can I use generate rows tool?
Thanks

[ChadM · Accepted solution]

Hi Evelyn,

This can absolutely be done using the Generate Rows Tool.  

I went about by first pulling in a Summarize Tool to find out the Min and Max dates available:

Once I had the min and max dates, I could easily create a formula within the Generate Rows Tool: 

Since the Summarize Tool would only output the [Min_date_attended] and [Max_date_attended], we need to create a new [date_attended] field.  The Generate Rows will add one day until the condition is met, which in this case is to reach the max date.  

Thank you!

Chad

[evelyn_sherwin]

Thanks Chad. That really helped.

Regards
Evelyn

[Rachel_Burge]

Do you know if you could do this same thing but for groups of  products? So, say you have several products in your data set with sales for each by month, but one product sold 0 units in that month. How would you generate that row with a 0 or Null value just for that product? The only way I can think to do this is by filtering out each individual product group, and then generating rows as shown in your post, but if you have more that 5 or 6 products that would get really messy, so I am hoping there is a better way to do it.

Thanks!

[RodL]

Rachel,

I would do something like the attached. It basically creates a Cartesian join of all product/date combinations and then you use a join to filter on the combinations that aren't in your original data set.

You would need to substitute in the "master date list".

[mattamidus]

Im using this solution but I keep getting the error the value did not change after the loop expression.

[davidhenington]

As always, Chad with the fantastic solution! 

[JamesBills]

I get the same issue. Did anyone have a reason for this? 

Thanks

[RodL]

@JamesBills,

I assume the issue is with the Generate Rows tool?

Typically if the value isn't changing, there's nothing in the Loop Expression that is incrementing the value. You might want to check your configuration there.

[JamesBills]

Yes issue was with Generate rows tool. I've rewritten loop expression a number of times and now it works! Thanks Rod