Convert monthly Budget to Daily Budget ?

[gauravgg]

Hi All

I have the following budget file in excel ,which is monthly budget for Plants.(for example i have took for only one plant)

I have to convert it into daily budget .

Budget:

load * inline

[

Plant,Month ,Value,Year

P1,Jan,100,2016

P1,Feb,100,2016

P1,Mar,100,2016

P1,Apr,100,2016

P1,May,100,2016

P1,Jun,100,2016

P1,Jul,100,2016

P1,Aug,100,2016

P1,Sep,100,2016

P1,Oct,100,2016

P1,Nov,100,2016

P1,Dec,100,2016

];

What i want to do is , for i want to sum(Value ) for P1 Plant and then divide it by 365 days i.e sum(Value)/365 group by Plant

sum(1200)/365 = 3.28

and also want date in that table.

So the final table should be as follows

FinalTable:

load * inline

[

Plant,Month,Value,Year,  Date,   DailyBudget

P1, Jan,  100,  2016,  01/01/2016, 3.28

P1, Jan,  100,  2016,  02/01/2016, 3.28

P1, Jan,  100,  2016,  03/01/2016, 3.28

P1, Jan,  100,  2016,  04/01/2016, 3.28

P1, Jan,  100,  2016,  05/01/2016, 3.28

P1, Jan,  100,  2016,  06/01/2016, 3.28

P1, Jan,  100,  2016,  07/01/2016, 3.28

P1, Jan,   100,  2016,  08/01/2016, 3.28

.

.

.

.

till

.

.

P1, Jan, 100  ,2016,  31/12/2016,  3.28

];

In My excel data is for multipal Plants,here for exemple i have showed only for one plant .

Can anyone help me in this

thanks in advance

[aarkay29 · Accepted solution]

Something Like this

Budget:

load * inline [

Plant,Month ,Value,Year

P2,Jan,200,2017

P2,Feb,200,2017

P2,Mar,200,2017

P2,Apr,200,2017

P2,May,200,2017

P2,Jun,200,2017

P2,Jul,200,2017

P2,Aug,200,2017

P2,Sep,200,2017

P2,Oct,200,2017

P2,Nov,200,2017

P2,Dec,200,2017

P1,Jan,100,2016

P1,Feb,100,2016

P1,Mar,100,2016

P1,Apr,100,2016

P1,May,100,2016

P1,Jun,100,2016

P1,Jul,100,2016

P1,Aug,100,2016

P1,Sep,100,2016

P1,Oct,100,2016

P1,Nov,100,2016

P1,Dec,100,2016

];

MaxDates:

Load

  (Makedate((MinYear),01,01)) as Min,

  (Makedate((MaxYear),12,31)) as Max;

Load

max(Year) as MaxYear,

min(Year) as MinYear

Resident

  Budget;

Trace; 

Let vStartDate = Num(Peek('Min',0,MaxDates));

Let vEndDate = Num(Peek('Max',0,MaxDates));

Drop Table MaxDates;

TempCalendar:

LOAD

  $(vStartDate) + RowNo() - 1       AS Num,

  date($(vStartDate) + RowNo() - 1) AS TempDate

AUTOGENERATE

  $(vEndDate) - $(vStartDate) + 1;

Calendar:

Load

  Num,

  TempDate as Date,

  Year(TempDate) as Year,

  Month(TempDate) as Month

Resident

  TempCalendar;

Drop Table TempCalendar;

Left join (Calendar)

Load

  Plant,

  Year,

  Sum(Value)/365 as Daily_Budget

Resident

  Budget

Group By

  Plant,

  Year;

;

Drop Table Budget;