Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Generate Date values

Highlighted
6 - Meteoroid
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
Highlighted
Alteryx Alumni (Retired)

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:


rtaImage (21).png

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


rtaImage (22).png

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

Highlighted
6 - Meteoroid
Thanks Chad. That really helped.

Regards
Evelyn
Highlighted
5 - Atom

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!

Highlighted
Alteryx Alumni (Retired)

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".

Highlighted
7 - Meteor

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

Highlighted
9 - Comet

As always, Chad with the fantastic solution! 

Highlighted
8 - Asteroid

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

 

Thanks

Highlighted
Alteryx Alumni (Retired)

@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.

Highlighted
8 - Asteroid

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

Labels