Generate Row to incorporate DATEDIFF

[DHB]

Does any one know how I can insert rows into my data that calculates the DATEDIFF between each Student's last Activity date and the End date in a new column?  In the example below I would like to generate a row after each of the rows with red text.  It would like it to duplicate the first 4 fields, have a null in the Activity field and 11 in the new column for Student 101 (DATEDIFF 31/01/16 and 20/01/16).  Is that possible?

Course	Start	End	Student	Activity
1	1/01/2016	31/01/2016	101	1/01/2016
1	1/01/2016	31/01/2016	101	10/01/2016
1	1/01/2016	31/01/2016	101	11/01/2016
1	1/01/2016	31/01/2016	101	12/01/2016
1	1/01/2016	31/01/2016	101	20/01/2016
1	1/01/2016	31/01/2016	202	2/01/2016
1	1/01/2016	31/01/2016	202	6/01/2016
1	1/01/2016	31/01/2016	202	11/01/2016
1	1/01/2016	31/01/2016	202	20/01/2016
1	1/01/2016	31/01/2016	202	31/01/2016
1	1/01/2016	31/01/2016	303	1/01/2016
1	1/01/2016	31/01/2016	303	20/01/2016
1	1/01/2016	31/01/2016	303	21/01/2016
1	1/01/2016	31/01/2016	303	22/01/2016
1	1/01/2016	31/01/2016	303	30/01/2016
2	1/01/2016	31/01/2016	101	10/01/2016
2	1/01/2016	31/01/2016	101	13/01/2016
2	1/01/2016	31/01/2016	101	19/01/2016
2	1/01/2016	31/01/2016	101	22/01/2016
2	1/01/2016	31/01/2016	101	23/01/2016
2	1/01/2016	31/01/2016	404	5/01/2016
2	1/01/2016	31/01/2016	404	6/01/2016
2	1/01/2016	31/01/2016	404	18/01/2016
2	1/01/2016	31/01/2016	404	24/01/2016
2	1/01/2016	31/01/2016	404	31/01/2016
2	1/01/2016	31/01/2016	505	9/01/2016
2	1/01/2016	31/01/2016	505	11/01/2016
2	1/01/2016	31/01/2016	505	21/01/2016
2	1/01/2016	31/01/2016	505	22/01/2016
2	1/01/2016	31/01/2016	505	30/01/2016

Thank tou in advance for your help.

DHB.

[Joe_Mako · Accepted solution]

How about the attached?

Sort - ensure correct sorting
Multi-Row Formula - Add an ActivityID field, for use sorting later
Sample - Last record per Course-Student combination
Formula - Calculate Difference in days, increment ActivityID, Null for Activity
Union - Append with data from before Sample
Sort - get final requested sort order

[DHB]

That works perfectly, thank you Joe.  I'd been trying to generate rows and it wasn't working.

[RodL · Accepted solution]

So I like to look at some of these questions and come up with an answer, but when I find that someone has already answered with a working solution like one of brilliant our ACEs, @Joe_Mako, I usually let it go.

But in this case, while the basic principle of creating a separate record and then unioning back with the original data set was also in my solution, I had approached the creation of the record a bit differently. And in the spirit of showing people that there are multiple ways to approach a problem, I figured I'd post my solution as well. Not that mine is better...just different...but I think we get the same result. 

[DHB]

Thank you for sharing your solution Rod.  I'm new to Alteryx so it's good to see differnet ways of doing things.

[Joe_Mako]

Nice work Rod!

I like how you sorted by Difference and then Activity to get the Null date last. I agree, it is great to see other routes. Thank you for not letting it go. I would love to see your take on other threads that I replied to. Thank you!

[DHB]

Following on from the above I'm now looking to convert the data so that each students activity in each course would be represented something like this;

Date	Date Number	Start	End	Activity	Activity Number	Student	Course
1/01/2016	1	1/01/2016	31/01/2016	1/01/2016	1	101	1
2/01/2016	2	1/01/2016	31/01/2016	null	null	null	null
3/01/2016	3	1/01/2016	31/01/2016	null	null	null	null
4/01/2016	4	1/01/2016	31/01/2016	null	null	null	null
5/01/2016	5	1/01/2016	31/01/2016	null	null	null	null
6/01/2016	6	1/01/2016	31/01/2016	null	null	null	null
7/01/2016	7	1/01/2016	31/01/2016	null	null	null	null
8/01/2016	8	1/01/2016	31/01/2016	null	null	null	null
9/01/2016	9	1/01/2016	31/01/2016	null	null	null	null
10/01/2016	10	1/01/2016	31/01/2016	10/01/2016	2	101	1
11/01/2016	11	1/01/2016	31/01/2016	11/01/2016	3	101	1
12/01/2016	12	1/01/2016	31/01/2016	20/01/2016	4	101	1
13/01/2016	13	1/01/2016	31/01/2016	null	null	null	null
14/01/2016	14	1/01/2016	31/01/2016	null	null	null	null
15/01/2016	15	1/01/2016	31/01/2016	null	null	null	null
16/01/2016	16	1/01/2016	31/01/2016	null	null	null	null
17/01/2016	17	1/01/2016	31/01/2016	null	null	null	null
18/01/2016	18	1/01/2016	31/01/2016	null	null	null	null
19/01/2016	19	1/01/2016	31/01/2016	null	null	null	null
20/01/2016	20	1/01/2016	31/01/2016	20/01/2016	5	101	1
21/01/2016	21	1/01/2016	31/01/2016	null	null	null	null
22/01/2016	22	1/01/2016	31/01/2016	null	null	null	null
23/01/2016	23	1/01/2016	31/01/2016	null	null	null	null
24/01/2016	24	1/01/2016	31/01/2016	null	null	null	null
25/01/2016	25	1/01/2016	31/01/2016	null	null	null	null
26/01/2016	26	1/01/2016	31/01/2016	null	null	null	null
27/01/2016	27	1/01/2016	31/01/2016	null	null	null	null
28/01/2016	28	1/01/2016	31/01/2016	null	null	null	null
29/01/2016	29	1/01/2016	31/01/2016	null	null	null	null
30/01/2016	30	1/01/2016	31/01/2016	null	null	null	null
31/01/2016	31	1/01/2016	31/01/2016	null	null	null	null

This shows Student 101 in Course 1 but I would like to generate a row for every day of the course for each student in whatever Course they're in.  I've been toiling away with only very modest progress so far.

Does anyone have any suggestions?

Thank you in advance for your help.

DHB

[Joe_Mako · Accepted solution]

Does the attached get you closer?

[DHB]

Thank you so much again Joe.  It got me all the way there.  I added a simple formula to the multiple join tool, sorted it after that and it works perfectly.

Best,

DHB