How to re-do a logic until satisfy the condition.

[Sivalingam]

Hello qlikers 

I am trying to develop a planning model in script, currently i have certain constrains ...

right now I have a table as below

Item	QTY_on hand	Ctime	Plan	Run time	Available_time_Perday	Optimized Run time
Item-1	1718	0.5	576	288	1224	?
Item-2	1323	0.5	576	288	1224	?

now i want to calculate optimized run time through loop by using the below condition and logic

Condition: sum (Current Optimized Run time) < Available_time_Perday 

Logic to run : current Optimized Run time + Run time 

stop condition: sum (Current Optimized Run time) > Available_time_Perday 

and need to load the output in Optimized Run time column against respective Item

[SRA]

Hi,

Not sure to understand what you need... Could you give us the result you expect as an example?

For these 2 lines, do you expect 1152 as a result ?

If yes, why not calculating this from the script with :

Round(Available_time_Perday/Run_time)*Run_time as Optimized_Run_time

You could also do it with a measure if needed

Regards,

SRA

[Sivalingam]

Hi SRA  - Thanks for your quick response - here with I given an example to show what I am expecting as an output.

							Optimized Run time
							Loop cycle need to be executed
Item	QTY_on hand	Ctime	Plan	Run time	Set up time	Available_time_PerWeek	During 1nd loop	During 2nd loop	During 3rd loop	During 4th loop	During 5th loop
Item-1	1718	0.5	1728	864	200	7344	1064	1928	2792	3656	-
Item-2	1323	0.5	1152	576	200		776	1352	1928	2504	-
Item-3	1323	0.5	576	288	200		488	776	1064	1184	-
							2328	4056	5784	7344	-
										Result Required

1	Here available time is common for total cell
2	During first loop only I want to add set up time
3	in next loops I want to add only run time with the original value wrt item until condition satisfy
4	if optimized run time exits available time in that case I want to add only the remaining time to meet the sum of optimized run time = available time Example - item-3 During 4th loop, sum of optimized run time (7512) exceeds available time (7344) in this case I want to add only 120 against item-3 instead of 576

[Sivalingam]

Hello qlikers  - is there anybody, can suggest a solution to solve the above issue.

[marcus_sommer]

It's not quite clear how your raw-data are look like and how the target-data should be to fulfill all view-requierements. I think you could use two approaches  - probably in some combinations.

This would be to use interrecord-functions like previous() and peek() within a properly sorted resident-load to query the previous loaded records within n nested if-loops and creating in extra columns information like a numbering, accumulation, offset-calculation, flagging and so on.

The above could provide a lot of valuable information but it won't create directly extra/separate records. If they are needed for any views they could be created with an internal while load-loop, for example with a logic like:

load *, value + iterno() as X resident Y while min + iterno() <= max;

Further helpful might be to extract any sub-sets of data and mapping/joining them back, for example min/max/count/concat aggregations in self-joins or concatenate-loads.

The main-challenge is to develop a working business-logic and to derive the needed execution-information to control the above hinted measurements. The technically implementation of the interrecord/loop/aggregation stuff is then rather simple.