Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All I have a Excel file having Data say Monthly basis . so i want to do on daily basis.
i have a Data like and EveryMonth data will increase like this.......................
YearMonth Channel SeValue SpValue
201603 A 960000 800000
201603 B 560000 4200000
201603 C 320000 4600000
201604 A 960000 800000
201604 B 560000 4200000
201604 C 320000 4600000
Output I needed..
YearMonth Channel Date SeValue ((Sevalue/NoofDaysinMonth) SpValue(SeValue / NoofDaysinmonth) NoofDayinMonth
201603 A 01-03-2016 30967.74 25806.45 31
201603 A 02-03-2016 30967.74 25806.45 31
........:
201603 A 31-03-2016 30967.74 25806.45 31
Same for All Channel....
201401 A 01-04-2016 30967.74 25806.45 30
and One more column
MaxDate
31 --- For March
31 --- For March
19 --- For April--(Today-1)
I am Attaching sample data and qvw kindly check... Currently i am able to do for CurrentMonth Only as my Script
For i=1 To Day(Today()-1)
LOAD YearMonth,
Channel,
SpValue,
SeValue,
Date(MakeDate(Year(Date#(YearMonth,'YYYYMM')),Month(Date#(YearMonth,'YYYYMM')),$(i))) as Link_Date,
Day(Today()-1) as TodayDay,
Day(MonthEnd(Today()-1)) as NoOfDayInMonth
FROM
(ooxml, embedded labels, table is Sheet1);
Next i;
Exit Script;
Don't use a For Loop, use a WHILE clause instead:
LOAD YearMonth,
DaysInMonth,
Channel,
SpValue,
SpValue / DaysInMonth as SpValueDaily,
SeValue / DaysInMonth as SeValueDaily,
Date( YearMonth+iterno()-1) as LinkDate
While iterno() <= DaysInMonth;
LOAD *,
If(YearMonth<Monthstart(Today()), Day(Monthend(YearMonth)), Day(Today()-1)) as DaysInMonth;
LOAD Date#(YearMonth,'YYYYMM') as YearMonth,
Channel,
SpValue,
SeValue
FROM
[For Loop.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE isNum(YearMonth);
Don't use a For Loop, use a WHILE clause instead:
LOAD YearMonth,
DaysInMonth,
Channel,
SpValue,
SpValue / DaysInMonth as SpValueDaily,
SeValue / DaysInMonth as SeValueDaily,
Date( YearMonth+iterno()-1) as LinkDate
While iterno() <= DaysInMonth;
LOAD *,
If(YearMonth<Monthstart(Today()), Day(Monthend(YearMonth)), Day(Today()-1)) as DaysInMonth;
LOAD Date#(YearMonth,'YYYYMM') as YearMonth,
Channel,
SpValue,
SeValue
FROM
[For Loop.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE isNum(YearMonth);
Well, this should be easy, like
Day(Monthend(YearMonth)) as NoofDaysInMonth
HI Swuehl ,
Thank you so much ....