Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
];
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
];
Thanks but my main problem is feb here it is making total 517 not 500
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
];
I need this code so urgent so that i am asking these type of question
Really thanks Sunny