Create a For Loop based on day in month

[brijeshvma]

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;

[swuehl · Accepted solution]

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);

[swuehl]

Well, this should be easy, like

Day(Monthend(YearMonth)) as NoofDaysInMonth

[brijeshvma]

HI Swuehl ,

Thank you so much ....