topic Re: Question on Load script with while statement in QlikView

Question on Load script with while statement

Mon, 18 Sep 2017 00:33:42 GMT

Hello Everyone,

I try to load project budget to each month between project start date and project end date.I have question on my loading script:

Here is loading script for daily budget. This one working fine.

LOAD [Proj ID] as GoalProj,
Year([Proj Start Dt]+iterno()-1) as GoalYear,
Month([Proj Start Dt]+iterno()-1) as GoalMonth,
num(Month([Proj Start Dt]+iterno()-1)) as GoalMonthNo,
Date([Proj Start Dt]+iterno()-1) as GoalDate,
[Proj Tot Value]/([Proj End Dt]-[Proj Start Dt]+1) as DailyGoalAmount
while ([Proj Start Dt]+iterno()-1) <= [Proj End Dt] ;

Here is monthly budget loading stript. This one is not working because the while statement is not right.

LOAD [Proj ID] as GoalProj,
Year([Proj Start Dt]+iterno()-1) as GoalYear,
Month([Proj Start Dt]+iterno()-1) as GoalMonth,
num(Month([Proj Start Dt]+iterno()-1)) as GoalMonthNo,
//Date([Proj Start Dt]+iterno()-1) as GoalDate,
[Proj Tot Value]/((year([Proj End Dt])*12+Month([Proj End Dt]))-(Year([Proj Start Dt])*12+month([Proj Start Dt]))+1 ) as MonthlyGoalAmount
while (num(Year([Proj Start Dt])*12+month([Proj Start Dt]))+iterno()-1) <= num(year([Proj End Dt])*12+Month([Proj End Dt])) ;

Can you help find why this monthly loading script is not working?

Thank you guys very much!

Best regards!

Re: Question on Load script with while statement

marcus_sommer — Mon, 18 Sep 2017 12:37:13 GMT

I think the while-condition should be look something like this:

while addmonths(monthstart([Proj Start Dt]), iterno() - 1)) < monthend([Proj End Dt]);

maybe with a bit adjusting to really needed dates which might be the exact date or any kind of monthstart/monthend.

Beside them I'm not sure if I would use such a monthly budget-table - why not using the table on the daily-level and then aggregating them on any needed period within the charts?

- Marcus

Re: Question on Load script with while statement

Mon, 18 Sep 2017 14:01:43 GMT

Thank you very much - Marcus.

Your solution looks very very smart. But It looks only run one time when I test this script. The result is like this:

Project ID	Proj Name	Proj Start Dt	Proj End Dt	Proj Tot Value	MonthlyGoalAmount	GoalMonth	GoalYear
BJ1-0001	Work TRANSER	12/12/2016	09/30/2017	2612383	261238.3	Dec	2016
BJ1-0002	Work TRANSER	12/12/2016	09/30/2017	221581	22158.1	Dec	2016
BJ1-0003	Work TRANSER	12/12/2016	12/31/2017	1208	92.92307692	Dec	2016
BJ1-0004	Work TRANSER	12/12/2016	12/31/2017	206204	15861.84615	Dec	2016
BJ1-0005	Work TRANSER	12/12/2016	12/31/2017	729686	56129.69231	Dec	2016
BJ1-0006	Work TRANSER	12/12/2016	12/31/2017	39541	3041.615385	Dec	2016
BJ1-0007	Work TRANSER	12/12/2016	12/31/2017	5031	387	Dec	2016
BJ1-0008	Work TRANSER	12/12/2016	12/31/2017	40009	3077.615385	Dec	2016
BJ1-0009	Work TRANSER	12/12/2016	12/31/2017	6699	515.3076923	Dec	2016
BJ1-0010	Work TRANSER	12/12/2016	12/31/2017	3256	250.4615385	Dec	2016
BJ1-0011	Work TRANSER	12/12/2016	12/31/2017	73066	5620.461538	Dec	2016
BJ1-0012	Work TRANSER	12/12/2016	12/31/2017	12291	945.4615385	Dec	2016
BJ1-0013	Work TRANSER	12/12/2016	12/31/2017	197628	15202.15385	Dec	2016
BJ1-0014	Work TRANSER	12/12/2016	12/31/2017	186244	14326.46154	Dec	2016
BJ1-0015	Work TRANSER	12/12/2016	12/31/2017	157816	12139.69231	Dec	2016
BJ1-0016	Work TRANSER	12/12/2016	12/31/2017	64216	4939.692308	Dec	2016
BJ1-0017	Work TRANSER	12/12/2016	12/31/2017	51059	3927.615385	Dec	2016

The reason I want to generate monthly goal is my revenue actual is by month.

Thank you very much for your inputs. Appreciate.

Best regards.

Re: Question on Load script with while statement

marcus_sommer — Mon, 18 Sep 2017 14:30:33 GMT

Monthly data could be treated as daily data just by converting the Year and Month into a date. For example with something like: makedate(Year, Month, 1) which made the handling quite easy. We use this approach since many years and concatenate the budget data just to the sales data whereby the comparing of both values is done with a monthly forecast of sales and the budget is already a monthly value.

- Marcus

Re: Question on Load script with while statement

Mon, 18 Sep 2017 14:50:12 GMT

Marcus,

You are awesome with so many good solutions on my question. We have monthly revenue actual. But for project budget is by project period based on project start date and project end date. So I try to average this budget by months and compare to actual.

For the loading script, the daily script is working and can get budget for each day. I just cannot understand why the monthly script is only run once when load.

Thank you again - Marcus. Your reply is greatly appreciated!

Re: Question on Load script with while statement

marcus_sommer — Tue, 19 Sep 2017 07:40:22 GMT

Like above mentioned I would quite probably go with a daily budget-value and aggregate the needed periods over them within the UI. I didn't test a while-loop to create monthly budget-values but I created two versions of them with aggregation-loads. Maybe it's helpful for you to find the most suitable approach for your use-case:

DailyBudget:

load *, MonthName(monthstart(Date)) as MonthName;

load

*, date(From + iterno() -1) as Date, [Proj Tot Value] / (To - From) as DailyBudget

while From + iterno() - 1 <= To;

load

[Project ID], [Proj Name], [Proj Tot Value], [MonthlyGoalAmount], [Proj Start Dt], [Proj End Dt],

date(date#([Proj Start Dt], 'MM/DD/YYYY')) as From, date(date#([Proj End Dt], 'MM/DD/YYYY')) as To

inline [

Project ID Proj Name Proj Start Dt Proj End Dt Proj Tot Value MonthlyGoalAmount GoalMonth GoalYear

BJ1-0001 Work TRANSER 12/12/2016 09/30/2017 2612383 261238.3 Dec 2016

BJ1-0002 Work TRANSER 12/12/2016 09/30/2017 221581 22158.1 Dec 2016

BJ1-0003 Work TRANSER 12/12/2016 12/31/2017 1208 92.92307692 Dec 2016

BJ1-0004 Work TRANSER 12/12/2016 12/31/2017 206204 15861.84615 Dec 2016

BJ1-0005 Work TRANSER 12/12/2016 12/31/2017 729686 56129.69231 Dec 2016

BJ1-0006 Work TRANSER 12/12/2016 12/31/2017 39541 3041.615385 Dec 2016

BJ1-0007 Work TRANSER 12/12/2016 12/31/2017 5031 387 Dec 2016

BJ1-0008 Work TRANSER 12/12/2016 12/31/2017 40009 3077.615385 Dec 2016

BJ1-0009 Work TRANSER 12/12/2016 12/31/2017 6699 515.3076923 Dec 2016

BJ1-0010 Work TRANSER 12/12/2016 12/31/2017 3256 250.4615385 Dec 2016

BJ1-0011 Work TRANSER 12/12/2016 12/31/2017 73066 5620.461538 Dec 2016

BJ1-0012 Work TRANSER 12/12/2016 12/31/2017 12291 945.4615385 Dec 2016

BJ1-0013 Work TRANSER 12/12/2016 12/31/2017 197628 15202.15385 Dec 2016

BJ1-0014 Work TRANSER 12/12/2016 12/31/2017 186244 14326.46154 Dec 2016

BJ1-0015 Work TRANSER 12/12/2016 12/31/2017 157816 12139.69231 Dec 2016

BJ1-0016 Work TRANSER 12/12/2016 12/31/2017 64216 4939.692308 Dec 2016

BJ1-0017 Work TRANSER 12/12/2016 12/31/2017 51059 3927.615385 Dec 2016

] (txt, delimiter is \t);

MonthlyBudget1:

load [Project ID], dual(date(floor(monthstart(Date)), 'YYYY-MM'), date(floor(monthstart(Date)))) as YearMonth1, sum(DailyBudget) as MonthlyBudget1

resident DailyBudget group by [Project ID], dual(date(floor(monthstart(Date)), 'YYYY-MM'), date(floor(monthstart(Date))));

left join (MonthlyBudget1)

load [Project ID], avg([Proj Tot Value]) / count(distinct MonthName) as MonthlyBudget2

resident DailyBudget group by [Project ID];

- Marcus

Re: Question on Load script with while statement

Tue, 19 Sep 2017 13:52:39 GMT

Dear Marcus,

Your solution looks great for my question. MonthlyBudget2 is looks right. I am going to implement your script to my app.

Thank you very much on your smart and nice solution. Wish you all the best.

Dust