How to convert month lines to day lines in a script

[SupportActiveProfessionals]

Hi,

I have a datasource with the following columns
Date, Budget (in euro)
1/8/2024,1000
1/9/2024,1500
1/10/2024,2000
The date is the first day of that month (DD/MM/YYYY). The amount is a total budget amount for that month

Now I want to load this data in a script and generate lines PER DAY.
So the end result of the table after executing the script should be

Date, Budget
1/8/2024, 32.26 (Calculated as 1000/31 as august has 31 days)
2/8/2024, 32.26
3/8/2024, 32.26
31/8/2024, 32.26
1/9/2024, 50 (calculated as 1500 / 30 as september has 30 days)
etc.
How do I do that?

[barnabyd · Accepted solution]

G'day,

There are a couple off ways to solve this. If you have a calendar table, you can left join the calendar into your data table and that will duplicate all the rows to the number of days in each month. If you don't have a calendar table, then you can use this strategy:

DailyBudget:
load Date + iterno() - 1 as [Daily Budget Date]
   , Budget / [Days In Month] as [Daily Budget]
while iterno() <= [Days In Month]
;
load Date
   , Budget 
   , floor( monthend( Date ) ) as [Days In Month]
resident MonthlyBudgetTable
;

I'm not sure if this code is perfect, but the gist of it is to use the monthend() function to find the days in the month and the 'load ... while ...' to iterate over the month.

I hope this makes sense. Cheers, Barnaby.

[MarcoWedel]

maybe using 

day( monthend( Date ) ) as [Days In Month]

instead