Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys!
Very interesting question, I couldn't find the answer in google yet. Please help or give an advice.
The situation:
I have table 1
Store | Item | Available | Checked |
---|---|---|---|
S1 | I1 | 0 | * |
S2 | I1 | 0 | * |
S3 | I1 | 2 | |
S4 | I1 | 0 | * |
S5 | I1 | 0 | |
T | I1 | 2 |
The task is to take Available values from store T and spread it into other Checked stores. It should spread only 1 item for each necessary store.
Finally table would look like:
Store | Item | Available | Checked | Finally |
---|---|---|---|---|
S1 | I1 | 0 | * | 1 |
S2 | I1 | 0 | * | 1 |
S3 | I1 | 2 | 2 | |
S4 | I1 | 0 | * | 0 |
S5 | I1 | 0 | 0 | |
T | I1 | 2 | 0 |
Also please help to make new row where I could write from which store I took item and where I put this item.
Try this..
T1:
LOAD * INLINE [
Store, Item, Available, Checked
S1, I1, 0, *
S2, I1, 0, *
S3, I1, 2,
S4, I1, 0, *
S5, I1, 0,
T, I1, 2,
];
temp:
Load Available Resident T1 where Store = 'T';
Let vAvailable = peek('Available');
DROP Table temp;
NoConcatenate
Final:
LOAD Store,Item,Available,Checked,
If(Store='T',0,
if(Available=0 and Checked='*' and RangeSum(1, Peek('Final'))<=$(vAvailable),1,Available)) as Final
Resident T1;
DROP Table T1;
Hi, then can modify in this way.
Previous:
if(Store='T',$(StockLeft),
if(Available = 0 and numsum(Checked,peek(Accu_Check,-1))<=$(Stock),1,0)
) as Final
Now:
if(Store='T',$(StockLeft),
if(Available = 0,
if(numsum(Checked,peek(Accu_Check,-1))<=$(Stock),1,0),
Available)
) as Final
Thank you! Fot this example it works!
But in case when there is number if items in Stock are more than in all zero Stocks, it should keep some remained items (items in stock - count of stocks with 0 values)
Thank you! I'll try!
Thank you for help! This works for me!