topic Re: Need to distribute Capacity value from starting date to till date in App Development

Need to distribute Capacity value from starting date to till date

Siraj — Tue, 10 Mar 2020 08:13:33 GMT

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

Re: Need to distribute Capacity value from starting date to till date

Taoufiq_Zarra — Tue, 10 Mar 2020 09:34:11 GMT

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;

Re: Need to distribute Capacity value from starting date to till date

Siraj — Tue, 10 Mar 2020 10:36:08 GMT

Hi Taoufiq,

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

Regards

Sirajahamed S