how to generate a monthly entry out of yearly value

[#1]

Hi,

I need some help with the following problem.

I have an excel sheet with a year column containing 2011, colum Product and a budget column containing the yearly budget.

I am searching for a way (scripting) to load the file per month, so adding a column in qlikview containing the values 1 till 12 and loading the Budget /12 without scripting this per month ( 12 sets of scripts)

Any suggestions?

Kind Regards,

Linda Monincx

[#2]

Hi Linda,

You can use filebasename() function which will give you base name of file.

So, suppose you name your excel file as   monthly_actual_12.xls 

then you can use subfield(filebasename(),3,'_') as month while loading this excel sheet in qlikview. that way you can keep adding excel sheets and you will be able to pick month from filename and year as is already there in your excel sheet.

hope this helps.

regards,

Ashutosh

[johnw]

Something like this?

MonthlyBudget:
LOAD

Product
,date(makedate(Year,iterno()),'MMM YYYY') as Month
,YearlyBudget/12 as MonthlyBudget
FROM YearlyBudgetSource
WHILE iterno()<=12
;