Fill in the missing data and make continuous sequences

[LEXQ2005]

Scenario: Students are taking online courses, the data source includes student name, hours of study, and the month student took classes.

Many students do not take classes every month. But I want to present continuous time series in the chart.

Below is the source data:

Student	Month No.	Hour
A	1	80
A	3	78
B	1	90
B	2	89
B	3	86
B	4	92
B	5	96
B	6	90
B	7	78
C	2	60
C	6	80
C	7	87
C	10	86
C	12	81

I want Alteryx to output a data set like this:

Student	Month No.	Hour
A	1	80
A	2
A	3	78
B	1	90
B	2	89
B	3	86
B	4	92
B	5	96
B	6	90
B	7	78
C	1
C	2	60
C	3
C	4
C	5
C	6	80
C	7	87
C	8
C	9
C	10	86
C	11
C	12	81

[Jean-Balteryx · Accepted solution]

Hi @LEXQ2005 ,

Here is a workflow doing what you need.

Basically it takes the maximum value for each student then generates rows from 1 to the max value and joins back hours to those values. The last union gather all rows.

[bensilv · Accepted solution]

Hi @LEXQ2005 

Please see the attached workflow.

Here I use generate rows to get all combinations, then a formula tool to overwrite the month column and replace hour with nulls (if it is a new row). Finally a unique to get the unique list.