Generate Date values
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Date Time
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Regards
Evelyn
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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".
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Im using this solution but I keep getting the error the value did not change after the loop expression.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
As always, Chad with the fantastic solution!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I get the same issue. Did anyone have a reason for this?
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Yes issue was with Generate rows tool. I've rewritten loop expression a number of times and now it works! Thanks Rod
