Generate data for those month which is not in table

[nisha_rai]

Hi ,

Please help me to resolve the below issue:

I have a table which is comming monthly bases, but not continuouly bases. for example : we are getting data for Jan but till Aprile we are not getting any data , so for those moth we have to repeat Jan data, other senario is may be we are not getting any data till december.

in that case we have to repeat the whole data for full year.

Example:

Month	Item	Sub item	Country	Act Sale	Forecast Sale
1	A	XYZ	India	0	30
1	B	XYZ	UK	0	25
1	A	PQR	USA	0	10
1	C	QWE	France	0	40
1	D	RST	UAE	0	20

Month	item	Sub Item	Country	Act Sale	Forcast Sale
1	A	XYZ	India	30	0
2	B	XYZ	UK	10	0
3	C	QWE	France	25	0
4	D	RST	UAE	20	0
5	A	PQR	USA	30	0
6	B	XYZ	UK	45	0
7	C	PQR	USA	40	0
8	D	QWE	France	15	0
9	A	RST	UAE	25	0

In the Final result I want to repeat the first table data till 9th month

Output Reult:

Month	Item	Sub item	Country	Act Sale	Forecast Sale
1	A	XYZ	india	30	30
1	B	XYZ	UK		25
1	A	PQR	USA		10
1	C	QWE	France		40
1	D	RST	UAE		20
2	A	XYZ	India		30
2	B	XYZ	UK	10	25
2	A	PQR	USA		10
2	C	QWE	France		40
2	D	RST	UAE		20

Like the above output our value will repeat till September.

Please help me to generate the data for moth which is not availble in table.

Thanks & Regards,

Nisha

[ahaahaaha]

Hi Nisha,

May be like at attached file.

Regards,

Andrey

[MK_QSL]

Your example and output table is not clear.. Can you provide proper output table?

[nisha_rai]

Hi Andrey,

Thanks for your response,  if suppose after jan we receive the Forecast table in Aprile then my output will be

Jan , Feb , March data be the replica of jan data but after March we have April data then April to September we have the replica of April data, then how we can achive this sceario .

Regards,

Nisha

[nisha_rai]

Hi Manish,

Below is my requirement:

Table1 Actual Data:( Table will time to time)

Item M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12

A      10   20 30 40 50 60 70 80 90 100 120 130

B       5   10 15 20 25 30 35 40 45 50 55 60

C       2 4 6 8 10 12 14 18 20 22 24 26

Table2  Forecast Data:( table not change regular bases, it will change based on the analysis)

Item Data Month

A 10  1

B 20  1

C 30  1

suppose Feb and march we are not getting any file, we are recive file in April

Table 2 Forecast Data:

Item  Data Month

A  25   4

B  35   4

C 45    4

Final Output For Table 2 Forecast table is:

Table2 Forecast Data:

Item Data Month

A 10  1

B  20 1

C 30 1

A 10 2

B 20 2

C  30 2

A  10  3

B   20  3

C   30   3

A    25  4

B 35    4

C 45  4

A   25   5

B   35   5

C   45   6

like that we have to repeat the data for rest of the month but in between month again if we receive the file  then the data will change for that month and rest of the month contain the same data from the latest month data.

Regards,

Nisha

[PrashantSangle]

You have to perform several steps to achieve it.

Logic is like

1: load your table

2: create Master Calender from min and max date of as per your table.

3: join calendar to your initial table

4: Take resident of new table i.e. output of your initial table join master calendar table then use Peek() to generate missing records.

Regards,

[nisha_rai]

Hi Prashant,

I tried that logic but it's not giving me a values for those month which is not present in the Forecast table.

Regards,

[PrashantSangle]

After applying logic what value it is giving to you for missing months???

Can post your script??

Regards

[PrashantSangle]

After applying logic what value it is giving to you for missing months???

Can post your script??

Regards

[nisha_rai]

if i am selecting feb,March then it's show zero, for Jan it's giving me 60.

Regards,