Need to distribute Capacity value from starting date to till date

[Siraj]

Hi,

I have Plant wise capacity volume with starting date, my task is to distribute the volume using starting date to till date.

Example : 

Plant	Capacity	SOP
AP	12500	Mar-19
Europe	30000	Aug-19

Need to convert the above table into below pivot table format 

Required :

Plant	Capacity	March 1, 2019	Apirl 2019	May 1, 2019	Jun 1,2019	July 1, 2019	August 1, 2019	Sep 1,2019	October 1, 2019	November 1, 2019	December 1, 2019	January 1, 2020	February 1, 2020	March 1, 2020
AP	12500	12500	12500	12500	12500	12500	12500	12500	12500	12500	12500	12500	12500	12500
Europe	30000	0	0	0	0	0	30000	30000	30000	30000	30000	30000	30000	30000

Thanks in advance 

Regards

Sirajahamed S

[Taoufiq_Zarra]

attached qvw and qvf version :

Data:
LOAD Date(Date#(SOP,'MMM-YY')) as Date,* INLINE [
    Plant, Capacity , SOP
    AP, 12500, Mar-19
    Europe, 30000, Aug-19
];

MinMaxDate:

	Load Min(Date) as MinDate, Makedate(Year(min(Date))+1,Month(min(Date)),1) as MaxDate resident Data;
	Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
	Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
	Drop Table MinMaxDate;
	
MasterCalendar:

	Load Date,
	Year(Date) as Year,
	Month(Date) as Month;
	Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

Temp:
noconcatenate

load Date(min(Date)) as Date2 resident MasterCalendar group by Year,Month;
drop table MasterCalendar;
join
load * resident Data;

drop table Data;


Result:
noconcatenate

Load Plant,Date(Date2,'MMM-YY') as New_SOP,if(Date2<Date,0,Capacity) as Capacity resident Temp;

drop table Temp;

[Siraj]

Hi Taoufiq,

Thanks for your reply. Will try this and let you know.

Regards

Sirajahamed S