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.
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
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.
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.
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".
Generating Records with Zero Sales for each Product.yxmd