generating Missing Months

[shetty_1]

Hi All,

I need a solution on the below logic

Actual Data:

Material No	month year	Value
10000111	Sep2018	3750
10000111	Oct2018	1500
10000111	Apr2019	0
10000111	Oct2019	10987

Expected Output:

Material No	month year	Value
10000111	Sep2018	3750
10000111	Oct2018	1500
10000111	Nov2018	1500
10000111	Dec2018	1500
10000111	Jan2019	1500
10000111	Feb2019	1500
10000111	Mar2019	1500
10000111	Apr2019	0
10000111	May 2019	0
10000111	June 2019	0
10000111	July 2019	0
10000111	Aug 2019	0
10000111	Sep 2019	0
10000111	Oct 2019	10987
10000111	Nov 2019	10987
10000111	Dec 2019	10987

Thanks In Advance

Regards,

S Shetty

[shivanisapkale]

Hello,

For getting the below expected output you need to create a master calendar for the month so that it will create a missing month year and you can create date as well in case you need it.

After creating month year sort the data in ascending order according to the MonthYear and then use if and previous function to take previous value for missing months.

Regards,

Shivani Sapkale

[shetty_1]

Hi Shivani,

Thanks for the solution.

Can you just send me the code? So that I can work on it. 

With the same data can you send me the code. So that it will be helpful for me.