Generate additional rows based on condition

[jpena]

Hi guys,

Need your help solving an issue here.

I have this input:

Product	Year	Month	Value
A	2018	12	0.6
A	2019	3	6.4
A	2019	4	9.7
A	2019	7	7.3
B	2018	8	8.5
B	2018	9	7.3
B	2018	11	4.0
B	2019	2	4.9
B	2019	4	1.9

And I need to turn it into the following output:

Product	Year	Month	Value
A	2018	12	0.6
A	2019	1	6.4
A	2019	2	6.4
A	2019	3	6.4
A	2019	4	9.7
A	2019	5	7.3
A	2019	6	7.3
A	2019	7	7.3
B	2018	8	8.5
B	2018	9	7.3
B	2018	10	4.0
B	2018	11	4.0
B	2018	12	4.9
B	2019	1	4.9
B	2019	2	4.9
B	2019	3	1.9
B	2019	4	1.9

The values in bold and italic are the new rows I want to generate.

Basically, what I need to happen is that for a given product I want to have all months represented from the starting period (Year and Month) until the last one and for that I need to add rows. Besides that, the newly generated rows should get the value (field [Value]) from the older period.

Thanks in advance!

[danilang · Accepted solution]

Hi @jpena 

Here's a workflow that does what you're looking for

Start by building a Start of Month (SoM) column from your input.  Summarize by product to find the min and max SoM.  Use the Generate Rows tool to fill in all the months.  Join back to your input data to pick up the Values that are there.  A Multi-Row tool will fill in the values but it only works from the top of the dataset set down.  Since you want to fill from the bottom up, sort by product and SoM descending first.  Fill the missing values and the next formula tool fills in the missing Year and Month fields.  Re-sort by product and date ascending and you have

Dan

[jpena]

Solved. Tks!