Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;