Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Generate Date values

evelyn_sherwin
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
12 REPLIES 12
ChadM
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

evelyn_sherwin
6 - Meteoroid
Thanks Chad. That really helped.

Regards
Evelyn
Rachel_Burge
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!

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

mattamidus
7 - Meteor

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

davidhenington
10 - Fireball

As always, Chad with the fantastic solution! 

JamesBills
8 - Asteroid

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

 

Thanks

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

JamesBills
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