topic Re: Convert monthly data by location and year to daily in QlikView

Convert monthly data by location and year to daily

regowins — Wed, 02 Mar 2016 03:19:28 GMT

Hi,

I have a budget table by month by location going back 3 years...( below & attached sample data) .

DPT_ID	MONTH_DATE	LOCATION	BUDGETED	DATE_YEAR	DATE_MONTH
1	1/1/2012	21	555	2012	1
2	2/1/2012	21	856	2012	2
3	3/1/2012	21	678	2012	3
4	4/1/2012	21	425	2012	4
5	5/1/2012	21	786	2012	5
6	6/1/2012	21	786	2012	6
7	7/1/2012	21	422	2012	7
8	8/1/2012	21	786	2012	8
9	9/1/2012	21	435	2012	9
10	10/1/2012	21	453	2012	10
11	11/1/2012	21	963	2012	11
12	12/1/2012	21	424	2012	12

I need to recreate the exact same table as above but convert the monthly budget to daily for each month by location. so for example, I would take Jan 555 / 31 = 17.90 and then generate 31 dates with 17.9 for daily budget ....Feb 856/29 = 29.5 for 29 days... till the end of the year. Then loop for the next location / Year and monthly budget to convert to daily.

Any idea how I would go about doing this in the script?

Thanks for the help!

Re: Convert monthly data by location and year to daily

jagan — Wed, 02 Mar 2016 03:38:11 GMT

Hi,

Try this

Data:

LOAD

DPT_ID,

MONTH_DATE,

LOCATION,

BUDGETED,

DATE_YEAR,

DATE_MONTH,

BUDGETED/ Num(MonthEnd - MonthStart + 1) AS Budget_Daily,

Date(MONTH_DATE + IterNo() - 1) AS DATE

WHILE Num(MonthStart + IterNo() - 1) <= MonthEnd;

LOAD DPT_ID,

Date(MONTH_DATE) AS MONTH_DATE,

LOCATION,

BUDGETED,

DATE_YEAR,

DATE_MONTH,

Num(MonthEnd(Date(MONTH_DATE))) AS MonthEnd,

Num(MonthStart(Date(MONTH_DATE))) AS MonthStart

FROM

(ooxml, embedded labels, table is [ Sample]);

Re: Convert monthly data by location and year to daily

regowins — Wed, 02 Mar 2016 05:02:03 GMT

Your solution was perfect! Thank You!!!!

Re: Convert monthly data by location and year to daily

tresB — Wed, 02 Mar 2016 06:24:40 GMT

Nice solution Jagan!

however, a small but significant bug is there in this solution.

BUDGETED/ Num(MonthEnd - MonthStart + 1) AS Budget_Daily,

MonthEnd would have last millisecond of the last date of the month which would result a difference (MonthEnd-MonthStart) by extra 1 (0.99..). That is for ex. - the January value will be divided by 32 (31.99...) instead of 31. You can handle this by two alternate ways:

BUDGETED/ (Floor(MonthEnd) - MonthStart + 1) AS Budget_Daily

Or,

BUDGETED/ Day(MonthEnd) AS Budget_Daily

Re: Convert monthly data by location and year to daily

regowins — Thu, 03 Mar 2016 16:07:50 GMT

Thanks for the catch Tresesco!