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!
Solved! Go to 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
Solved. Tks!