Yearly data per month in a load script

[a-5]

Hi,

In the data source that I'm using, I have the budget data on annual level. However, I need to populate that data into monthly level by creating a period column (1-12) and budget monthly column (annual budget divided by 12). 

Any idea how to do this in the load script/data load editor?

Thanks!

[henrikalmen · Accepted solution]

You could for example do it by creating a table with all months, and then do an outer join with 12th:s of the budget. Something like this:

annual:
load * inline [
account, budget
1234, 125000
1235, 12000
];

monthly:
load * inline [
month
1
2
3
4
...
];

join (monthly) load account, budget/12 as monthbudget resident annual;

[a-5]

Hi Henrik! 

Thank you for reply - this logic works in principle, but when I add the logic into my data model, the data model includes monthly targets only until March 2024 (because I have actual sales records until that month). However, I would like to include all the budgets of all upcoming months during 2024 as well (so I need to include monthly targets for Apr-Dec too).

Any idea how to get this implemented into the data model?

Thank you in advance!

[henrikalmen]

You need to add the missing months to your data model. How you do that depends on various parameters... In my basic example you could for example first do something like this to add months before the joining:

concatenate(montly) load * inline [
month
5
6
];

It's not necessarily the best thing to do with your actual data, but it could work...