Distribute Month values to Date

[agni_gold]

Hi friends,

suppose i have

2016     Jan     200 USD

2016     Feb     500 USD

I want to distribute month values to all dates and amount should divided by 31 for jan and 29 days for feb and all dates should come.

[sunny_talwar]

May be like this:

Table:

LOAD Date(Date + IterNo() - 1) as Date,

  CURRENCY,

  AMOUNT_PER_DAY

While Date + IterNo() - 1 <= EndDate;

LOAD *,

  Floor(MonthEnd(Date)) as EndDate,

  AMOUNT/(Floor(MonthEnd(Date)) - MonthStart(Date)) as AMOUNT_PER_DAY;

LOAD *,

  Date(MonthStart(Date#(MONTH & '-' & YEAR, 'MMM-YYYY'))) as Date;

LOAD * INLINE [

YEAR, MONTH, AMOUNT, CURRENCY

    2016,    Jan,    200, USD

    2016,    Feb,    500, USD

];

[agni_gold]

Thanks but my main problem is feb here it is making total 517 not 500

[sunny_talwar · Accepted solution]

How about now:

Table:

LOAD Date(Date + IterNo() - 1) as Date,

  CURRENCY,

  AMOUNT_PER_DAY

While Date + IterNo() - 1 <= EndDate;

LOAD *,

  Floor(MonthEnd(Date)) as EndDate,

  AMOUNT/(Floor(MonthEnd(Date)) - MonthStart(Date) + 1) as AMOUNT_PER_DAY;

LOAD *,

  Date(MonthStart(Date#(MONTH & '-' & YEAR, 'MMM-YYYY'))) as Date;

LOAD * INLINE [

YEAR, MONTH, AMOUNT, CURRENCY

    2016,    Jan,    200, USD

    2016,    Feb,    500, USD

];

[agni_gold]

I need this code so urgent so that i am asking these type of question

Really thanks Sunny