Record for each date from start date to end date

[kevinbird15]

What I'm trying to do is take a record that looks like this: 

Start_DT	End_DT	Unit_ID
2013-04-05	2013-04-09	1

and change it to look like this: 

Record_DT	Unit_ID
2013-04-05	1
2013-04-06	1
2013-04-07	1
2013-04-08	1
2013-04-09	1

Is this possible with Alteryx?  I have had a difficult time making this work.  Any help would be appreciated.  Currently I don't have any ideas that would make this work.  One thing I thought about is that I could calculate how many dates were between the start and end date and then add N records, but I'm not sure how I could do this in Alteryx.  

Thanks again.  

[michael_treadwell · Accepted solution]

Try using the Generate Rows tool. See the attached example.

[kevinbird15]

Thanks Michael, I was actually just replying to this because I realized that this was sort of similar to this other post from me (Which you also solved for me).  I was actually able to come up with this solution based off of that thread.  Thanks for the help in both cases.  

[dan_b]

This solution worked for me, but I'm running into an issue. I'm trying to do increments of one month, but if the last Record Date month is greater than the End date, it just generates the previous month. For example, if the start date is 5/27/09 end date is 2/5/10, the last month on the record date is 1/27/10. Here is the formula I'm using...

DATETIMEADD([Record Date], 1, 'month')

Do you have a suliton for that? 

[kevinbird15]

Hey Dan, 

You need to change your Condition Expression if you want that last record date included.  This worked for me.  Let me know if there is anything still not working.  

DateTimeTrim([Record_DT],"month") <= DateTimeTrim([End_DT],"month")

[dan_b]

That worked! Thank you!!

[rajm]

Hey hi, I have a similar problem, But I have multiple Units_ID with multiple start and end dates, I wante to blow out dates from all start date to enddate for each specific group.  

The generate rows works fine only for one group/Units_ID. How do we do the similar operation for multiple Units_ID/groups and multiple start & end dates.?

Thank you.

[kevinbird15]

Can you give a bit more information?  Maybe what your current table looks like and what you are wanting it to look like?

Thanks

[rajm]

Hi Kevinbird15, thank you for reaching out.

My current version would like this  and have multiple products.The first row contains the column names

Item Startdate enddate price
A 10-Jun 13-Jun $10
A 14-Jun 15-Jun $20
B 10-Jun 11-Jun $15
B 12-Jun 12-Jun $25
 

I would like to this to be as below,The first row contains the column names

Item Pricedate price
A 10-Jun $10
A 11-Jun $10
A 12-Jun $10
A 13-Jun $10
A 14-Jun $20
A 15-Jun $20
B 10-Jun $15
B 11-Jun $15
B 12-Jun $25

Hope that clarifies.

[kevinbird15]

Here you go rajm.  I used the alteryx dates so I didn't have to convert those into dates but this should do it.