Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi Taoufiq,
Thanks for your reply. Will try this and let you know.
Regards
Sirajahamed S