Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
a-5
Contributor III
Contributor III

Yearly data per month in a load script

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!

Labels (2)
1 Solution

Accepted Solutions
henrikalmen
Specialist
Specialist

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;

View solution in original post

3 Replies
henrikalmen
Specialist
Specialist

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
Contributor III
Contributor III
Author

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
Specialist
Specialist

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...