Populate missing months

[#1]

Hi All,

I have a table with data like below,  in the table for the unit 7511 I am not getting some of the months data because of I didn't have sales for that unit on the messing month. however I would like to show ,unit number, missing month,  '0' in the Sold column how do I get this, can any one help me on this.

Actual:  

Unit	Month	Actual
7511	Sep 2014	139232
7511	Nov 2014	17023
7511	Dec 2014	7177
7511	Jan 2015	6827
7511	Feb 2015	2738
7511	Mar 2015	13245
7511	Apr 2015	12523
7511	Jun 2015	20164
7511	Jul 2015	5890
7511	Aug 2015	5071
7511	Sep 2015	11891
7511	Oct 2015	2757
7511	Nov 2015	7036
7511	Dec 2015	4195
7511	Jan 2016	6372

Expected

Unit	Month	Actual
7511	Sep 2014	139232
7511	Oct 2014	0
7511	Nov 2014	17023
7511	Dec 2014	7177
7511	Jan 2015	6827
7511	Feb 2015	2738
7511	Mar 2015	13245
7511	Apr 2015	12523
7511	May 2015	0
7511	Jun 2015	20164
7511	Jul 2015	5890
7511	Aug 2015	5071
7511	Sep 2015	11891
7511	Oct 2015	2757
7511	Nov 2015	7036
7511	Dec 2015	4195
7511	Jan 2016	6372

[#2]

Pradeep sorry for my mistake , I have attached wrong test data earlier , please find the correct file above.

[PradeepReddy · Accepted solution]

see the attachment...

I have restricted the data for 3 units only(7511, 7512, 7513), bcz of size issue. you can remove the condition in script..

[#4]

thank you so much Pradeep taniparthy , could you please explain me the concept behind entire logic that you have written.

[PradeepReddy]

is this the expected result?

[#6]

Yes you saved my time. , that is exactly what I want.

please explain me the script that you have written,i a, curious about the  last iterno()  section what is happening there?

[PradeepReddy]

Glad to help..

I am Calculating the no.days between the two sale dates of same Unit. (Jan-01-2015, Jan-03-2015 =2)

After that I am  generating the missing dates between those 2 dates, by using loop.The loop will be iterated till 'no.of Days' calculated for each sale date entry

For 1st Iteration (Jan-03-2015)-1+1 --> Jan-3-2015

For 2nd Iteration (Jan-03-2015)-2+1 --> Jan-2-2015

For Accumulated values, I have used the Peek() function..

[#8]

Thank you so much.

[#9]

Pradeep I have followed the script procedure you have provided , however I am getting

General Script Error

Execution Failed      after Iteration, to pick previous Accumulated value .

[sunny_talwar]

Can you share your script?

[PradeepReddy]

can u share your QVW?