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

[nisha_rai]

TempTable_FTE:
Load date(MakeDate('2017',PlanMthNum),'DD\MM\YYYY') as FTE_For_Date,* from table1.qvd
(qvd);

MinMaxDate:
Load Min(FTE_For_Date) as MinDate_FTE,
Max(FTE_For_Date) as MaxDate_FTE
Resident TempTable_FTE;
Let vMinDate_FTE =peek('MinDate_FTE',-1,'MinMaxDate');
Let vMaxDate_FTE = peek('MaxDate_FTE',0,'MinMaxDate_FTE');

join(TempTable_FTE)
Load Date(recno()+$(vMinDate_FTE)) as FTE_For_Date  AutoGenerate $(vMaxDate_FTE) - $(vMinDate_FTE);

Forecast_FTE:
NoConcatenate
Load *,
if(IsNull(M12),Peek(M12),M12) as FTE_Forcast1
Resident TempTable_FTE
order by FTE_For_Date;

drop table MinMaxDate, TempTable_FTE;

[d_prashanthredd]

Hi Nisha,

Try this.. @ How to populate a sparsely populated field

I hope, it will help you.

[nisha_rai]

Tried the same but i am not getting result

Regards,

[PrashantSangle]

Hi,

Sorry for late reply

in peek() try below

if(IsNull(M12),Peek(FTE_Forcast1),M12) as FTE_Forcast1

Regards,

[nisha_rai]

Thanks Prashant,

It's repeating the data but for only one product not for all product.

Regards,

[MK_QSL]

Temp:
CrossTable(Month,Value)
Load * Inline
[
Item, 1, 2,  3, 4, 5, 6, 7, 8, 9, 10, 11, 12
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
];


NoConcatenate
T1:
Load Item, Num#(Month,'#0') as Month, Value Resident Temp;


Drop Table Temp;




TempT2:
Load Item, Data, Month, Item&'|'&Month as Key Inline
[
Item, Data, Month
A, 10,  1
B, 20,  1
C, 30,  1
A,  25,  4
B,  35,  4
C, 45,  4
A,  125, 9
B,  130, 9
C, 140, 9
];


Temp:
Load Distinct Item Resident T1;
Join
Load Distinct Month Resident T1;


Concatenate(TempT2)
Load * Where Not Exists (Key);
Load 
Item,
Month,
Item&'|'&Month as Key
Resident Temp; 


Drop Table Temp;
Drop Field Key;


NoConcatenate
T2:
Load 
Item, 
Month,
If(Item = Peek(Item) and IsNull(Data), Peek(Data), RangeSum(Data)) as Data  
Resident TempT2 Order By Item, Month;


Drop Table TempT2;

[d_prashanthredd]

Hi Nisha,

Yes, we are getting data only for one item when we tried so. Below is working fine.. Have a look at it and let me know if any problem.

MonthsData:

load * Inline [

Item,Data,Month

A,10,1

B,20,1

C,30,1

];

load * Inline [

Item,Data,Month

A,25,4

B,35,4

C,45,4

];

MinMaxMonth:

Load Min(Month) as MinMonth, Max(Month) as MaxMonth resident MonthsData;

Let vMinMonth = Peek('MinMonth',-1,'MinMaxMonth') - 1;

Let vMaxMonth = Peek('MaxMonth',-1,'MinMaxMonth')    ;

join (MonthsData)

MissingData:

Load Item, ($(vMinMonth) + IterNo()) as Month Resident MonthsData

While IterNo() <= ($(vMaxMonth)-1) - ($(vMinMonth)-1);

Rates:

NoConcatenate Load Item, Month,

If( IsNull( Data ), Peek( Data ), Data ) as Data

Resident MonthsData

Order By Item, Month ;

drop table MinMaxMonth, MonthsData;

Output:

[nisha_rai]

Hi Prashanth,

It's not giving me the value when  more columns are added in the table.

[nisha_rai]

Hi Manish,

This also not giving me the correct value, even it's not populated the month which are missing.

[MK_QSL]

Can you give an example from my above script, which item is not giving output for which month?