community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Record for each date from start date to end date

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.  

Alteryx Certified Partner
Alteryx Certified Partner

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

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.  

Meteor

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? 

 

Record Date.PNG

 

 

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

Meteor

That worked! Thank you!!

Atom

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.

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

 

Thanks

Atom

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.

Highlighted

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

Labels