The final column calculated in such way:
1) It finds 0 values in column Available and sign '*' in column Checked. Let's call them 'Wanted'
2) Looks for values in store T to put them into 'Wanted' stores
3) We can put maximum 1 item into each store.
So, we have 'Wanted' stores - S1,S2,S4
We have available items in store T equal to 2.
We need to spread the values from T to S1,S2,S4.
So we take 1 from T to S1, we take 1 from T to S2. Then we see that T is empty. We finish spreading process.
Finally, we have:
You can do this :
Let t_Count = Load Available resident Table1 where Store = 'T';
Let vIter = Load count(Checked) resident Table1 where Checked = 1;
for i = 1 to vIter
Load *, 1 as Finally resident Table1 where Checked =1 and $(t_Count) >0;
Let t_Count = t_Count-1;
Load *, 0 as Finally resident Table1 where Checked =1 and Finally <>1;
Hope this works.
First peek the value from T keep this in a variable var1.
Next resident load the table with an if saying
If(condition=* and available =0), var2 =var2 +1,
0 as flag.
This will give you a variable var2 with no of stores you want to divide the value of T.
Next resident load again
For I = 0 to var1
peek (third table, rowno() = i
If(condition=* and available =0) and flag <var1
then finally = flag+1 and flag=flag+1;
Var1 =var1- 1;
You have to repeat the for loop if var1 > count(*) in the table.
Sorry I cannot give you the exact code, but this will give you some idea, if you can share a qvw, I may check the feasibility of my pseudo code too.
You may want to try this. Final is the result table.
LOAD * INLINE [
Store, Item, Available
S1, I1, 0
S2, I1, 0
S3, I1, 2
S4, I1, 0
S5, I1, 0
T, I1, 2
If(Available = 0,1,0) as Checked
Count(Store) as EmptyStores
where Available = 0;
Let EmptyStores = peek('EmptyStores');
Available as Stock
where Store = 'T';
Let Stock = peek('Stock');
let StockLeft = if($(Stock) - $(EmptyStores)<0,0,$(Stock) - $(EmptyStores));
numsum(Checked,peek(Accu_Check,-1)) as Accu_Check,
if(Available = 0 and numsum(Checked,peek(Accu_Check,-1))<=$(Stock),1,0)
) as Final
drop table Raw, RawTemp0, RawTemp1, RawTemp2;
LOAD * INLINE [
Store, Item, Available, Checked
S1, I1, 0, *
S2, I1, 0, *
S3, I1, 2,
S4, I1, 0, *
S5, I1, 0,
T, I1, 2,
Load Available Resident T1 where Store = 'T';
Let vAvailable = peek('Available');
DROP Table temp;
if(Available=0 and Checked='*' and RangeSum(1, Peek('Final'))<=$(vAvailable),1,Available)) as Final
DROP Table T1;